CODEWITHSUNDEEP
sqloracle-databasepivotunpivotlistagg
JustLift
JustLift

Reputation: 173

Merge multiple columns values in one column in one row Oracle SQL

I have got multiple columns that needs to be aggregated into column in a single row.

Before 

Table name: columnMerger
colNum col1 col2 col3
1      a    b    c

After

colNum      col1234
1           a, b, c

Step 1, I used unpivot to bring all in one column 

 select colNum, value from columnMerger unpivot (value for node (col1, col2, col3));

Result, 

colNum  value
1       a
1       b
1       c

Step 2, Brought listagg, to merge the columns however I get error

"single-row subquery returns more than one row"

select colNum, listagg((
select distinct value from columnMerger unpivot (value for node (col1, col2, col3)), ',') 
within group (order by colNum) from columnMerger group by colNum;

Any help would be grateful, thanks.

Upvotes: 2

Views: 18074

Answers (2)

David Faber
David Faber

Reputation: 12485

While you don't need to use UNPIVOT in this case - and it probably is overkill - to use LISTAGG() with your UNPIVOT query you'll want to do the following:

SELECT colnum, LISTAGG(value, ',') WITHIN GROUP ( ORDER BY value ) AS col1234
  FROM (
    SELECT colnum, value
      FROM columnmerger
    UNPIVOT (
        value for node IN (col1, col2, col3)
    )
) GROUP BY colnum;

The reason, I think, you're getting the error with LISTAGG() is that the function expects a scalar value for each row. (You were also missing an IN in your UNPIVOT clause.)

If you need distinct values then you'll want something like the following:

SELECT colnum, LISTAGG(value, ',') WITHIN GROUP ( ORDER BY value ) AS col1234
  FROM (
    SELECT DISTINCT colnum, value
      FROM columnmerger
    UNPIVOT (
        value for node IN (col1, col2, col3)
    )
) GROUP BY colnum;

Hope this helps.

Upvotes: 2

shrek
shrek

Reputation: 887

You don't need a listagg for this, you can just concat all the columns as follows - 

select colnum, col1||','||col2||','||col3 as col1234
from columnMerger

COLNUM  COL1234
1       a,b,c

Upvotes: 3

Related Questions