This question already has an answer here:

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

marked as duplicate by Andriy M, Tom V, Vérace, Paul White Jan 14 '16 at 22:46

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Is the number of groups per col_id finite? – Philᵀᴹ Jan 14 '16 at 13:09
  • Oh, and there's no logical way to ORDER your Col_Group in the row – Philᵀᴹ Jan 14 '16 at 13:17

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.


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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.