CODEWITHSUNDEEP
sqloracle-databaseconcatenationcase
Cheddar Smith
Cheddar Smith

Reputation: 21

CONCATENATE a CASE in Oracle SQL

I need to run a CASE expression on a number of columns, the columns are Boolean, so if it's 0 I need to populate the column with the column name and if it's 1, I ignore the column/value. I then need to concatenate all these columns into one. Is it possible to do this in Oracle SQL?

I've tried this:

Select
    ||CASE
       WHEN COL_A = 0 THEN 'COL_A'
       ELSE ''
    END||','
    ||CASE
       WHEN COL_B = 0 THEN 'COL_B'
       ELSE ''
    END||
from ABC.123

Can this even been done? If not this way are there any other ways?

Upvotes: 2

Views: 22567

Answers (1)

DCookie
DCookie

Reputation: 43523

Yes, it will work (if you clean up the syntax). Here's a simple example:

with q as (
select 0 col_a, 1 col_b, 'Rec 1' id from dual
union all
select 1, 0, 'Rec 2' from dual
union all
select 0, 0, 'Rec 3' from dual
)
Select id, 
    CASE
       WHEN COL_A = 0 THEN 'COL_A'
       ELSE ''
    END||','
    ||CASE
       WHEN COL_B = 0 THEN 'COL_B'
       ELSE ''
    END "TheString"
from q

Result:

ID      TheString
------- -------------------   
Rec 1   COL_A,
Rec 2   ,COL_B
Rec 3   COL_A,COL_B

Upvotes: 5

Related Questions