select
tmp.PF_CODE as "都道府県コード",
max(PF_NAME) as "都道府県名",
max(case tmp.rank when 1 then tmp.NATION_NAME else null end) as "1位 国名",
max(case tmp.rank when 1 then tmp.AMT else null end) as "1位 人数",
max(case tmp.rank when 2 then tmp.NATION_NAME else null end) as "2位 国名",
max(case tmp.rank when 2 then tmp.AMT else null end) as "2位 人数",
max(case tmp.rank when 3 then tmp.NATION_NAME else null end) as "3位 国名",
max(case tmp.rank when 3 then tmp.AMT else null end) as "3位 人数",
sum(AMT) as "合計人数"
from
(
select
p.PF_CODE,
PF_NAME,
NATION_NAME,
AMT,
rank() over(partition by p.PF_CODE order by AMT desc) as RANK
from PREFECTURE as p
join FOREIGNER as f on p.PF_CODE = f.PF_CODE
left join NATIONALITY as n on f.NATION_CODE = n.NATION_CODE
) as tmp
group by PF_CODE
order by sum(AMT) desc, PF_CODE