select
  prefecture.pf_code as 都道府県コード,
  pf_name as 都道府県名,
  max(case rank when 1 then nation_name else '' end) as  "1位 国名",
  sum(case rank when 1 then amt else 0 end) as "1位 人数",
  max(case rank when 2 then nation_name else '' end) as "2位 国名",
  sum(case rank when 2 then amt else 0 end) as "2位 人数",
  max(case rank when 3 then nation_name else '' end) as "3位 国名",
  sum(case rank when 3 then amt else 0 end) as "3位 人数",
  sum(amt) as 合計人数
from prefecture
join (
  select
    RANK() OVER(partition by pf_code ORDER BY sum(amt) DESC, nationality.nation_code asc) as rank,
    pf_code,
    nationality.nation_code,
    nation_name,
    sum(amt) as amt
  from foreigner
  join nationality
  on foreigner.nation_code = nationality.nation_code
  where nationality.nation_code <> '113'
  group by
    pf_code, nationality.nation_code, nation_name
  order by
    sum(amt) desc, nationality.nation_code asc
) as tmp
on tmp.pf_code = prefecture.pf_code
group by
  prefecture.pf_code, pf_name
order by
  合計人数 desc, 都道府県コード asc;