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  fn.pf_code as 都道府県コード,
                pf.pf_name as 都道府県名,
                fn.nation_code as 国コード,
                nn.nation_name as 国名,
                fn.amt as 人数,
                rank() over (partition by fn.pf_code order by sum(fn.amt) desc,fn.nation_code asc) as 順位
        from foreigner as fn
        inner join nationality as nn on fn.nation_code = nn.nation_code
        inner join prefecture as pf on fn.pf_code = pf.pf_code
        where fn.nation_code != "113"
        group by 都道府県コード,国コード
    ) as ranking
group by 都道府県コード,都道府県名
order by 合計人数 desc,都道府県コード asc;