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

Question

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.

Answer 1

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