with pf_rank as (
    select
        FOREIGNER.PF_CODE,
        FOREIGNER.NATION_CODE,
        NATIONALITY.NATION_NAME as nation_name,
        FOREIGNER.AMT,
        rank() over(
            partition by FOREIGNER.PF_CODE
            order by FOREIGNER.AMT desc, FOREIGNER.NATION_CODE asc) as amt_rank
    from
        FOREIGNER
        join NATIONALITY on FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
    where
        FOREIGNER.NATION_CODE != '113'  -- 国籍が 'その他'
    order by PF_CODE, amt_rank
) select
    pf_rank.PF_CODE as 都道府県コード,
    PREFECTURE.PF_NAME as 都道府県名,
    max( case pf_rank.amt_rank when 1 then pf_rank.nation_name else null end ) as '1位 国名',
    max( case pf_rank.amt_rank when 1 then pf_rank.AMT         else 0 end )    as '1位 人数',
    max( case pf_rank.amt_rank when 2 then pf_rank.nation_name else null end ) as '2位 国名',
    max( case pf_rank.amt_rank when 2 then pf_rank.AMT         else 0 end )    as '2位 人数',
    max( case pf_rank.amt_rank when 3 then pf_rank.nation_name else null end ) as '3位 国名',
    max( case pf_rank.amt_rank when 3 then pf_rank.AMT         else 0 end )    as '3位 人数',
    sum( pf_rank.AMT ) as 合計人数
from
    pf_rank
    join PREFECTURE on PREFECTURE.PF_CODE = pf_rank.PF_CODE
group by pf_rank.PF_CODE
order by 合計人数 desc, pf_rank.PF_CODE asc
;