How to convert rows to columns group by multiple columns in Oracle sql

Question

I have a table with data as below:

a | b | c | d 
a | b | c | e 
a | b | c | f 
g | h | i | j 
g | h | i | k 
g | h | i | l 

I want to convert the data as below:

a | b | c | d | e | f
g | h | i | j | k | l

Tried as below. But it didn't work.

select col1, col2, col3,
       rtrim(xmlagg(xmlelement(e, col4 || ',')).extract ('//text()'), ',') val
from TABLEA
group by col1, col2, col

Could you please help me on this?

Answer 1

You can enumerate rows having the same values in the first three columns with row_number(), then pivot with conditional aggregation.

Assuming that the columns are called c1 to c4:

select c1, c2, c3,
    max(case when rn = 1 then c4 end) c41,
    max(case when rn = 2 then c4 end) c42,
    max(case when rn = 3 then c4 end) c43
from (
    select t.*, row_number() over(partition by c1, c2, c3 order by c4) rn
    from mytable t
) t
group by c1, c2, c3