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

 

[KenCunningham]

Ram, have you tried anything yet?

 

[PatelRam]

Not yet. Just trying to figure out the logic. Thanks. Ram

 

[ddiamond]

SELECT 
  ColumnA,
  SUBSTR(MIN(LPAD(rownum,'0',10) || ColumnB),11) || ':'
  SUBSTR(MAX(LPAD(rownum,'0',10) || ColumnB),11) AS ColumnB
FROM (
  SELECT rownum, ColumnA, ColumnB
  FROM TableA) x
GROUP BY ColumnA
ORDER BY 1, 2
;

This assumes you will have exactly 2 columnb's for each columna.

 

[PatelRam]

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

 

[ddiamond]

Ram,
Are you also using oracle? If not, why did you post this to an oracle forum? I noticed you posted the same query to a db2 forum.

- Dan