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.

Solution 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

Solution 2

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.

Comments

[JustLift]

Awesome. Also is there way where I can all columns in one statement (using wildcard or such i.e. select colnum, column_names like col% as col1234....).

[shrek]

Nope, not that I am aware of.

[David Faber]

@JustLift, you could do that in PL/SQL by querying the data dictionary, then using dynamic SQL.

[JustLift]

Thanks @David Faber, its Awesome this does the job too.

[JustLift]

@DavidFaber, Taking the second answer given below, I was thinking of replacing co1, col2, col3 and write, select column_name from all_tab_columns where table_name='columnMerger' and owner='ownerName' and column_name like 'col%'. However cannot process, not sure if you can bring select statement here.

[David Faber]

@JustLift, that SELECT statement probably wouldn't work unless your object names were actually in mixed case. But what you would have to do is take the results of that SELECT, put into a string, and then process using the DBMS_SQL package.

[JustLift]

I wonder how we can not sort out when we are doing listagg and unpivot. For example, if col 1 has b, col 2 and c and col 3 has a. I want to list the values as it is (b, c, a) however it is sorting out and giving me (a, b, c)

[JustLift]

@shrek, Hi, just a quick question lets say if the value of col 3 is null, can we do something that we can ignore the null ones but still call them in initial stage.

[shrek]

WITH A AS( SELECT 'A' AS C1, 'B' AS C2, NULL AS C3, NULL AS C4, 'E' AS C5, NULL AS C6 FROM DUAL ) SELECT RTRIM(REGEXP_REPLACE(C1||','||C2||','||C3||','||C4||','||C5|‌​|','||C6,'[,]+',',')‌​,',') FROM A;

[shrek]

Output is - A,B,E

[David Faber]

@JustLift you could try ORDER BY NULL but I don't know that it would work