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?

Since rows in a table are not ordered in any particular way (even though they may **seem** to be ordered when you view them), it is not clear how you decided that `d` should be placed in the fourth column, `e` in the fifth and `f` in the last column. How do you decide that? Or do you not care - any order is OK? — , Sep 11 '20 at 19:15
Then: for every distinct combination of the first three columns, will there always be exactly three different rows in the input (resulting in exactly three more columns being needed)? If the number of rows with the same three values in the first three columns can vary, then the problem doesn't even make sense - you will need to clarify the requirement, since in a relational database, all rows in an output must have the same number of columns. — , Sep 11 '20 at 19:16

score 2 · Accepted Answer · answered Sep 11 '20 at 18:48

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

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

1 Answers1