select 
    PF_CODE, -- → 都道府県コード
    PF_NAME, -- → 都道府県名
    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