I have below table
I want to create a SQL query to create a field which will concatenate the field's name with first column wherever we have the value "Yes" and only show one value in one row -
Output should be like this
James|Denver
James|Houston
James|Orlando
Williom|Denver
Williom|Houston
Ron|Chicago
Ron|Dallas
Ron|Austin
Saviz|Chicago
Saviz|Houston
How can I achieve this?
You can write a query like below with case and coalesce
select NAME,coalesce(case when Chicago="Yes" then "Chicago" else null end,
case when Denver="Yes" then "Denver" else null end,
case when Dallas="Yes" then "Dallas" else null end,
case when Houston="Yes" then "Houston" else null end,
case when Austin="Yes" then "Austin" else null end,
case when Orlando="Yes" then "Orlando" else null end) from tdf
with cities as
(
select 'chicago' city union all
select 'denver' union all
select 'dallas' union all
select 'houston' union all
select 'austin' union all
select 'orlando'
),
user_data as
(
select 'James' name, 'NO' chicago, 'Yes' denver, 'NO' dallas, 'Yes' houston, 'NO' austin, 'Yes' orlando union all
select 'Williom' name, 'NO' chicago, 'Yes' denver, 'NO' dallas, 'Yes' houston, 'NO' austin, 'NO' orlando union all
select 'Ron' name, 'Yes'chicago, 'NO' denver, 'Yes'dallas, 'NO' houston, 'Yes'austin, 'NO' orlando union all
select 'Savis' name, 'Yes'chicago, 'NO' denver, 'NO' dallas, 'Yes' houston, 'NO' austin, 'NO' orlando
)
select user_data_normalized.name,
cities.city
from
(
select name, 'chicago' city, chicago attr from user_data union all
select name, 'denver', denver from user_data union all
select name, 'dallas', dallas from user_data union all
select name, 'houston', houston from user_data union all
select name, 'austin', austin from user_data union all
select name, 'orlando', orlando from user_data
) user_data_normalized
join cities on cities.city = user_data_normalized.city
where user_data_normalized.attr = 'Yes'
order by 1,2
