select
    sub.pf_code as '都道府県コード',
    --pf_name as '都道府県名',
    max(case when rank_num = 1 then nation_name else 0 end) as '1位 国名',
    max(case when rank_num = 1 then amt else 0 end) as '1位 人数',
    max(case when rank_num = 2 then nation_name else 0 end) as '2位 国名',
    max(case when rank_num = 2 then amt else 0 end) as '2位 人数',
    max(case when rank_num = 3 then nation_name else 0 end) as '3位 国名',
    max(case when rank_num = 3 then amt else 0 end) as '3位 人数',
    sum(amt) as '合計人数'
from (
    select
        pf_code,
        nation_name,
        amt,
        rank() over(partition by pf_code order by amt desc) as rank_num
    from
        foreigner
        inner join nationality on foreigner.nation_code=nationality.nation_code
    where
        foreigner.nation_code <> '113'
    order by
        pf_code,
        rank_num
    ) as sub
group by
    sub.pf_code
order by
    合計人数 desc,
    都道府県コード
    ;