neverwinter neverwinter - 10 days ago 4
SQL Question

duplicate column during pivot table

I have a table like

select * from myTable

ID Type Prop1 Prop2 Prop3 Prop4 Prop5
-- ------ ------- ------- ------- ------- -------
1 Hot 10 9 23 32 4
1 Cold 2 24 53 34 5
2 Hot 11 9 23 32 4
2 Cold 22 1 53 30 11


I want to pivot my table like that

select * from myPivotTable

ID HotProp1 HotProp2 HotProp3 HotProp4 HotProp5 ColdProp1 ColdProp2 ColdProp3 ColdProp4 ColdProp5
-- ------- ------- ------- ------- -------- --------- --------- --------- --------- ---------
1 10 9 23 32 4 2 24 53 34 5
2 11 9 23 32 4 22 1 53 30 11


How can I convert myTable to myPivotTable using pivot function in oracle sql?

Answer

If your Oracle version 11g and up you can simply "re-pivot" it(You have already pivoted data) - unpivot it first and then pivot again:

with t1(id1, type1, Prop1, Prop2, Prop3, Prop4, Prop5) as(
  select 1,   'Hot' ,  10 ,  9 , 23,32 , 4 from dual union all
  select 1,   'Cold',  2  ,  24, 53,34 , 5  from dual union all
  select 2,   'Hot' ,  11 ,  9 , 23,32 , 4  from dual union all
  select 2,   'Cold',  22 ,  1 , 53,30 , 11 from dual 
  )
 select *
   from( select *
           from t1
        unpivot (
          val for col in (prop1, prop2, prop3, prop4, prop5)
        )
   )
   pivot(
     max(val) for (col, type1) in (('PROP1', 'Hot') as HotProp1, 
                                   ('PROP2', 'Hot') as HotProp2, 
                                   ('PROP3', 'Hot') as HotProp3,
                                   ('PROP4', 'Hot') as HotProp4,
                                   ('PROP5', 'Hot') as HotProp5,
                                   ('PROP1', 'Cold') as ColdProp1,
                                   ('PROP2', 'Cold') as ColdProp2,
                                   ('PROP3', 'Cold') as ColdProp3,
                                   ('PROP4', 'Cold') as ColdProp4,
                                   ('PROP5', 'Cold') as ColdProp5)
   ) 

Result:

      ID1   HOTPROP1   HOTPROP2   HOTPROP3   HOTPROP4   HOTPROP5  COLDPROP1  COLDPROP2  COLDPROP3  COLDPROP4  COLDPROP5
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         10          9         23         32          4          2         24         53         34          5
         2         11          9         23         32          4         22          1         53         30         11

Here is the demo

Comments