CODEWITHSUNDEEP
sqloracle-databasegroup-byconcatenationaggregate-functions
Mario Douh
Mario Douh

Reputation: 49

How can combine the values of multiple columns to a single comma separated column in Oracle SQL?

I need to create a column that has all the values from the previous columns combined and separated using commas ', '

I can't use listagg since I'm trying to combine multiple columns instead of rows.

below is an example of how the result column should look like, thanks.

Example

Upvotes: 0

Views: 1618

Answers (2)

Mario Douh
Mario Douh

Reputation: 49

This is also using a string concatenation but slightly different

SELECT 
SUBSTR(
    REPLACE(
    ', ' || NVL(column1,'!!') || ', ' || NVL(column2,'!!') || ', ' || NVL(column3,'!!')
    ,', !!','')
,3,100)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use string concatenation:

select trim(leading ',' from
             (case when column1 is not null then ',' || column1 end) ||
             (case when column2 is not null then ',' || column2 end) ||
             (case when column3 is not null then ',' || column3 end)
           )

Upvotes: 1

Related Questions