select
fore.pf_code as "都道府県コード",
pref.pf_name as "都道府県名",
max(case when rank() over (partition by fore.pf_code, fore.nation_code order by fore.amt, fore.nation_code) = 1 then nat.nation_name else null end) as "1位 国名",
sum(case when rank() over (partition by fore.pf_code, fore.nation_code order by fore.amt, fore.nation_code) = 1 then fore.amt else null end) as "1位 人数",
max(case when rank() over (partition by fore.pf_code, fore.nation_code order by fore.amt, fore.nation_code) = 2 then nat.nation_name else null end) as "2位 国名",
sum(case when rank() over (partition by fore.pf_code, fore.nation_code order by fore.amt, fore.nation_code) = 2 then fore.amt else null end) as "2位 人数",
max(case when rank() over (partition by fore.pf_code, fore.nation_code order by fore.amt, fore.nation_code) = 3 then nat.nation_name else null end) as "3位 国名",
sum(case when rank() over (partition by fore.pf_code, fore.nation_code order by fore.amt, fore.nation_code) = 3 then fore.amt else null end) as "3位 人数",
sum(amt) as "合計人数"
from
foreigner fore
inner join prefecture pref
on fore.pf_code = pref.pf_code
inner join nationality nat
on fore.nation_code = nat.nation_code 
group by
fore.pf_code
order by
"合計人数" desc,
"都道府県コード"