SQL Query

[PatelRam]

Hello,

I have a table which contains following data.

TableA:

ColumnA ColumnB

A1 C1
A1 C2
A2 C3
A2 C4


I need to build query which give me follwing output :

A1 C1:C2
A2 C3:C4

Can someone help me out.

Thanks,

Ram

 

[MarcLodge]

Ram,

What's your operating platform? Windows, mainframe etc. and what flavour of DB2 are you using?

Marc

 

[LFI]

select a.ColumnA, a.ColumnB||':'||b.ColumnB
from myTable a, myTable b
where a.ColumnA = b.ColumnA
and a.ColumnB != b.ColumnB

This will probably return
A1 C1:C2
A1 C2:C1
...

If the Column B values are orderable, you might consider:

select a.ColumnA, a.ColumnB||':'||b.ColumnB
from myTable a, myTable b
where a.ColumnA = b.ColumnA
and a.ColumnB [b]<[/b] b.ColumnB

I don't have a table like yours to test this on, but I think this would get you there.

The assumption with my query (if it even works) is that you only want rows in your result set where each value in Column A is represented exactly twice in the table. Where values in Column A are represented only once, I don't believe anything would come back and for values represented more than once, you are likely to get all of the various combinations of two unequal values in Column B.

'hope that helps.

Dave

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
...east is east and west is west and if you take cranberries and stew them like applesauce
they taste much more like prunes than rhubarb does

 

[PatelRam]

Thanks Guys for your input.... Sorry for the late reply. Finally I did it with programatically using assential datastage. I am using db2 ver 8.0 on aix