select 
    tmp.PF_CODE as "都道府県コード",
    p.PF_NAME as "都道府県名",
    max(case when rank = 1 then n.NATION_NAME else 0 end) as "1位 国名",
    max(case when rank = 1 then tmp.AMT else 0 end) as "1位 人数",
    max(case when rank = 2 then n.NATION_NAME else 0 end) as "2位 国名",
    max(case when rank = 2 then tmp.AMT else 0 end) as "2位 人数",
    max(case when rank = 3 then n.NATION_NAME else 0 end) as "3位 国名",
    max(case when rank = 3 then tmp.AMT else 0 end) as "3位 人数",
    sum(AMT) as "合計人数"
from
(
    select 
        PF_CODE,
        NATION_CODE,
        AMT,
        rank() over (partition by PF_CODE order by AMT desc, NATION_CODE asc) as rank
    from FOREIGNER
    where NATION_CODE <> '113'
) as tmp
    inner join PREFECTURE as p
        on tmp.PF_CODE = p.PF_CODE
    inner join NATIONALITY as n
        on tmp.NATION_CODE = n.NATION_CODE
group by tmp.PF_CODE
order by 
    "合計人数" desc,
    tmp.PF_CODE asc
;