I need to convert all rows into columns i.e. no.of columns= total no. of values in col2 and col3 related to col1.
Scenario
Query tried:
with cte as ( select 'A' as col1, 1 as col2, 2 as col3 from dual union select 'A' as col1, 3 as col2, 4 as col3 from dual union select 'A' as col1, 5 as col2, 6 as col3 from dual union select 'B' as col1, 10 as col2, 101 as col3 from dual union select 'B' as col1, 20 as col2, 202 as col3 from dual union select 'C' as col1, 50 as col2, 501 as col3 from dual union select 'C' as col1, 60 as col2, 601 as col3 from dual union select 'C' as col1, 70 as col2, 701 as col3 from dual ) select * from cte
How can I write a pivot query here?
Advertisement
Answer
use row_number()
and conditional aggregation
select col1, max(case when rn=1 then col2 end) as col2, max(case when rn=2 then col2 end) as col3, max(case when rn=3 then col2 end) as col4, max(case when rn=1 then col3 end) as col5, max(case when rn=2 then col3 end) as col6, max(case when rn=3 then col3 end) as col7 from ( select *, row_number() over(partition by col1 order by null) as rn from tablename )A group by col1