Skip to content
Advertisement

SQL query to convert no. of rows to no. of column in oracle

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

enter image description here

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
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement