Concatenation based on field values : SQL-CodePudding

I have a below table

I want to create a SQL query to create a field which will concatenate the field's name wherever we have the value "Yes"

Output should be like this

James|Denver|Houston|Orlando

Williom|Denver|Houston

Ron|Chicago|Dallas|Austin

Saviz|Chicago|Houston

How can I achieve it

CodePudding user response：

You should find a better table design I would say. But if you need to use exactly the same structure please try this,

SELECT
replace(
concat(NAME,'|'
,CASE WHEN Chicago='Yes' then "Chicago" end,'|'
,CASE WHEN Denver='Yes' then "Denver" end,'|'
,CASE WHEN Dallas='Yes' then "Dallas" end,'|'
,CASE WHEN Houston='Yes' then "Houston" end,'|'
,CASE WHEN Austin='Yes' then "Austin" end,'|'
,CASE WHEN Orlando='Yes' then "Orlando" end
),'||','|') as Output
from
yourtable