How to transpose rows to column in datatabse

Question

I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below

 Col_ID   Col_Group   Col_Price
 1        AA           0
 1        BB           2000
 1        CC           1000
 2        AA           0
 2        RR           3000
 2        DD           4000
 2        EE           0

and I want to make transpose or rows to column like as below

col_ID  group1 Price1 group2 Price2 Group3  Price3  Group4 Price4
 1       AA     0       BB    2000   CC      1000    
 2       AA     0       RR    3000    DD     4000      EE     0

I use PIVOT And trued below query

SELECT * FROM tbl1 PIVOT ( SUM(Col_Price) FOR Col_Group IN ('AA','BB','CC','DD','EE','RR'))

I got table But I Want as above mention. please Help me

score 1 · Answer 1 · answered Jan 14 '16 at 13:21

I can't do hard PIVOTs, it's just one of those things that breaks my brain. You need @bluefeet for that.

Anyway, you can do it the oldskool way with case ... when and max() if the number of groups per col_id is finite (I'm assuming the Col_Group names are unknown, and there's a finite number:

select Col_Id, max(Group1),max(Price1),max(Group2),max(Price2),max(Group3),max(Price3),max(Group4),max(Price4),max(Group5),max(Price5)
from (
  select Col_Id, case when Col_NewGroup=1 then Col_Group else null end as Group1, 
                 case when Col_NewGroup=1 then Col_Price else null end  as Price1, 
                 case when Col_NewGroup=2 then Col_Group else null end as Group2, 
                 case when Col_NewGroup=2 then Col_Price else null end as Price2, 
                 case when Col_NewGroup=3 then Col_Group else null end as Group3, 
                 case when Col_NewGroup=3 then Col_Price else null end as Price3, 
                 case when Col_NewGroup=4 then Col_Group else null end as Group4, 
                 case when Col_NewGroup=4 then Col_Price else null end as Price4, 
                 case when Col_NewGroup=5 then Col_Group else null end as Group5, 
                 case when Col_NewGroup=5 then Col_Price else null end as Price5
  from ( select Col_Id, Col_Price, Col_Group, row_number() over 
       ( 
           partition by Col_Id order by Col_Group
       ) as Col_NewGroup
       from tbl1 
     ) 
)
group by Col_Id;

Note that there's no way of ordering which groups end up where, as there's no ORDER given in your question.

score 0 · Answer 2 · answered Jan 14 '16 at 16:56

You need to add a grouping ID to your data. You can do that with the row_number analytic function in a sub factored query, then you can use the generated row number as the value to group your column groups by:

with t1 as (
select tbl.*
     , row_number() over (partition by col_id order by col_group) rn
  from tbl1
)
select *
  from t1
pivot (max(col_group) "GROUP", sum(col_price) PRICE FOR rn IN (1, 2, 3, 4))

You may need to adjust the order by clause in the analytic function to get the col_group codes to appear in your desired column locations, but I'll leave that as an exercise for the user.

How to transpose rows to column in datatabse

2 Answers2