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)