Denormalize a table

[SN]

Hi,

How can I denormalize a table with multiple rows into a single row with multiple attributes?
Is there a way to convert Table1 into Table2 using SET operators?

Ex:
Table 1:
COL_A COL_B COL_C
X A 1
X A 2
X A 3
X B 4
X B 5
X B 6

Table 2:
COL_A COL_B COL_C1 COL_C2 COL_C3
X A 1 2 3
X B 4 5 6

-SN

[Someshnr]

If your COL_C is a numeric field and there is a limited combination of COL_A, COL_B & COL_C in table 1 then you can use this principle....

Insert into Table2
Select A.COL_A, A.COL_B, SUM(COL_C), SUM(ID4), SUM(ID5)
FROM (
Select COL_A AS COL_A, COL_B AS COL_B
, CASE WHEN rank() over (Partition By COL_A,COL_B ORDER BY COL_C) =1
THEN COL_C ELSE NULL
END COL_C1
, CASE WHEN rank() over (Partition By COL_A,COL_B ORDER BY COL_C) =2
THEN COL_C ELSE NULL
END COL_C2
, CASE WHEN rank() over (Partition By COL_A,COL_B ORDER BY COL_C) =3
THEN COL_C ELSE NULL
END COL_C3
from Table1) A
group by 1,2