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