with tmp as (
select 
PF_CODE  pf_code
,nation_code nation_code
,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,f.nation_code asc) ranked
from tmp f inner join NATIONALITY n ON f.nation_code=n.nation_code
inner 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