with sum_f as (
select 
f.pf_code,
p.pf_name,
f.nation_code,
n.nation_name,
sum(f.amt) as sum_amt,
row_number() over (partition by f.pf_code order by sum(f.amt) desc,f.nation_code) as ranking
from foreigner as f
inner join prefecture as p
on f.pf_code = p.pf_code
inner join nationality as n
on f.nation_code = n.nation_code
where f.nation_code !='113'
group by 
f.pf_code,p.pf_name,f.nation_code,n.nation_name)
select
pf_code as 都道府県コード,
pf_name as 都道府県名,
max(case ranking when 1 then nation_name else null end) as "1位 国名",
max(case ranking when 1 then sum_amt else null end) as "1位 人数",
max(case ranking when 2 then nation_name else null end) as "2位 国名",
max(case ranking when 2 then sum_amt else null end) as "2位 人数",
max(case ranking when 3 then nation_name else null end) as "3位 国名",
max(case ranking when 3 then sum_amt else null end) as "3位 人数",
sum(sum_amt) as 合計人数
from sum_f
group by pf_code,pf_name
order by 合計人数 desc,pf_code