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
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
| 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
| 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
| 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 |
Use below query
select col1,rtrim( col2||','||col3||','||col4||','||col5,' ,') as col2 from table_name
there's probably a better way of handling the trailing comma, but this should work
SELECT col1,
col2 || decode(col3,'','',',') || col3 || decode(col4,'','',',') || col4 || decode(col5,'','',',')|| col5 as "col2"
FROM table
Use concatenation ||
Select Col1|| ',' ||Col2|| ',' ||Col2|| ',' ||Col3|| ',' || Col4 As OneCol
From Table;
