K.Vadim K.Vadim - 4 months ago 21
SQL Question

How to change columns with rows in db2 using join and row_number?

I have a table:

Need to get smth like this:

I already have a solution, but need to make it using joins and row_number():

select city, coalesce(max(case when id_name=1 then name end),'nobody') as name1, coalesce(max(case when id_name=2 then name end),'nobody') as name2, coalesce(max(case when id_name=3 then name end),'nobody') as name3 from city_name group by city order by city desc

Thanks in advance.


Here is a solution with a rownumber but it is not the best solution because you do not know in advance how many users (and therefore of column you can have)

with cityuserwithrow as (
select f1.*, rownumber() over(partition by f1.city order by f1.name) as rang
from city_name f1
select distinct f0.city, 
ifnull(f1.name, 'NOBODY') as NAME1, 
ifnull(f2.name, 'NOBODY') as NAME2, 
ifnull(f3.name, 'NOBODY') as NAME3
from city_name f0
left outer join cityuserwithrow f1 on (f0.city, 1)=(f1.city, f1.rang)
left outer join cityuserwithrow f2 on (f0.city, 2)=(f2.city, f2.rang)
left outer join cityuserwithrow f3 on (f0.city, 3)=(f3.city, f3.rang)