select 
    PF_CODE AS '都道府県コード', -- → 
    PF_NAME AS '都道府県名', -- → 都道府県名
    max(CASE rank WHEN 1 THEN NATION_NAME ELSE NULL END) AS '1位 国名',
    sum(CASE rank WHEN 1 THEN AMT ELSE 0 END) AS '1位 人数',
    max(CASE rank WHEN 2 THEN NATION_NAME ELSE NULL END) AS '2位 国名',
    sum(CASE rank WHEN 2 THEN AMT ELSE NULL END) AS '2位 人数',
    max(CASE rank WHEN 3 THEN NATION_NAME ELSE NULL END) AS '3位 国名',
    sum(CASE rank WHEN 3 THEN AMT ELSE NULL END) AS '3位 人数',
    sm AS '合計人数'
from (
select
    pre.PF_CODE,
    pre.PF_NAME,
    nat.NATION_NAME,
    for.AMT,
    sum(for.AMT) over (partition by pre.PF_CODE) as sm,
    row_number() over (partition by pre.PF_NAME order by for.AMT desc , for.NATION_CODE desc) as rank
from PREFECTURE pre
inner join FOREIGNER for
on pre.PF_CODE = for.PF_CODE
and for.NATION_CODE <> '113'
inner join NATIONALITY nat
on nat.NATION_CODE = for.NATION_CODE
) 
where rank <= 3
group by PF_CODE
order by sm desc, PF_CODE