with tmp as (
select 
PF_CODE  pf_code
,nation_code nation_code
,sum(amt) pop
from foreigner 
where nation_code<>'113'
group by 1,2),
tmp2 as (
select 
f.pf_code,
PF_NAME,
n.nation_name,
pop
, rank() over ( Partition by f.pf_code order by pop desc) ranked

from tmp f left join NATIONALITY n ON f.nation_code=n.nation_code
left join prefecture p on f.pf_code=p.pf_code)


select pf_code 都道府県コード,pf_name 都道府県名,
max(case when ranked=1 then nation_name else 0 end) '1位 国名',
max(case when ranked=1 then pop else 0 end) '1位 人数',
max(case when ranked=2 then nation_name else 0 end) '2位 国名',
max(case when ranked=2 then pop else 0 end) '2位 人数',
max(case when ranked=3 then nation_name else 0 end) '3位 国名',
max(case when ranked=3 then pop else 0 end) '3位 人数',
sum(pop) 合計人数
from tmp2
group by 1,2
order by 合計人数 desc,都道府県コード asc