with w as (
    select 
        f.PF_CODE,
        f.NATION_CODE,
        f.AMT,
        n.NATION_NAME,
        p.PF_NAME, 
        rank() over(partition by f.PF_CODE order by f.AMT desc, f.NATION_CODE asc) as RANK
    from 
        FOREIGNER as f
    join
        NATIONALITY as n 
        on f.NATION_CODE = n.NATION_CODE and n.NATION_CODE != '113'
    join
        PREFECTURE as p
        on f.PF_CODE = p.PF_CODE
)

select 
    PF_CODE as 都道府県コード,
    PF_NAME as 都道府県名,
    max(case when RANK = 1 then NATION_NAME else null end) as '1位 国名',
    sum(case when RANK = 1 then AMT else 0 end)  as '1位 人数',
    max(case when RANK = 2 then NATION_NAME else null end)  as '2位 国名',
    sum(case when RANK = 2 then AMT else 0 end)  as '2位 人数',
    max(case when RANK = 3 then NATION_NAME else null end) as '3位 国名',
    sum(case when RANK = 3 then AMT else 0 end)  as '3位 人数',
    sum(AMT) as 合計人数
from
    w
group by 
    PF_NAME, PF_CODE
order by
    合計人数 desc, 都道府県コード asc
;