Concatenate values from multiple columns in Oracle

Question

I'm working with an oracle database and what I basically need to concatenate in one column, values from multiple columns for every row.

Something like this:

col1    col2    col3    col4    col5
____________________________________
 1       A       B       C       D
 2       A       B       C
 3       C       A
 4       D       A       C

col1    col2
____________
 1      A,B,C,D
 2      A,B,C
 3      C,A
 4      D,A,C

Solution 1

Use below query

   select col1,rtrim( col2||','||col3||','||col4||','||col5,' ,') as col2  from table_name

Solution 2

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test (col1, col2, col3, col4, col5 ) AS
          SELECT 1, 'A', 'B', 'C', 'D' FROM DUAL
UNION ALL SELECT 2, 'A', 'B', 'C', NULL FROM DUAL
UNION ALL SELECT 3, 'C', 'A', NULL, NULL FROM DUAL
UNION ALL SELECT 4, 'D', 'A', 'C', NULL FROM DUAL
UNION ALL SELECT 5, NULL, NULL, NULL, NULL FROM DUAL
UNION ALL SELECT 6, NULL, NULL, NULL, 'A' FROM DUAL
UNION ALL SELECT 7, 'B', NULL, NULL, 'A' FROM DUAL
UNION ALL SELECT 8, NULL, 'C', NULL, 'A' FROM DUAL;

Query 1:

If there are no NULL values between other values (it will introduce multiple commas in rows 7 & 8):

SELECT col1,
       TRIM( ',' FROM col2||','||col3||','||col4||','||col5 ) AS col2
FROM   test

Results:

| COL1 |    COL2 |
|------|---------|
|    1 | A,B,C,D |
|    2 |   A,B,C |
|    3 |     C,A |
|    4 |   D,A,C |
|    5 |  (null) |
|    6 |       A |
|    7 |   B,,,A |
|    8 |    C,,A |

The last two queries will work for all examples:

Query 2:

SELECT col1,
       TRIM( ',' FROM col2 || NVL2( col3, ','||col3, NULL ) || NVL2( col4, ','||col4, NULL ) || NVL2( col5, ','||col5, NULL ) ) AS col2
FROM   test

Results:

| COL1 |    COL2 |
|------|---------|
|    1 | A,B,C,D |
|    2 |   A,B,C |
|    3 |     C,A |
|    4 |   D,A,C |
|    5 |  (null) |
|    6 |       A |
|    7 |     B,A |
|    8 |     C,A |

Query 3:

SELECT col1,
       REGEXP_REPLACE( col2||','||col3||','||col4||','||col5, '(^|,),+|,+($)', '\1' ) AS col2
FROM   test

Results:

| COL1 |    COL2 |
|------|---------|
|    1 | A,B,C,D |
|    2 |   A,B,C |
|    3 |    C,A, |
|    4 |   D,A,C |
|    5 |  (null) |
|    6 |       A |
|    7 |     B,A |
|    8 |     C,A |