SELECT
 t.PF_CODE as '都道府県コード'
,t.PF_NAME as '都道府県名'
,MAX(CASE t.RNK WHEN 1 THEN t.NATION_NAME ELSE 0 END) as '1位 国名'
,MAX(CASE t.RNK WHEN 1 THEN t.AMT ELSE 0 END) as '1位 人数'
,MAX(CASE t.RNK WHEN 2 THEN t.NATION_NAME ELSE 0 END) as '2位 国名'
,MAX(CASE t.RNK WHEN 2 THEN t.AMT ELSE 0 END) as '2位 人数'
,MAX(CASE t.RNK WHEN 3 THEN t.NATION_NAME ELSE 0 END) as '3位 国名'
,MAX(CASE t.RNK WHEN 3 THEN t.AMT ELSE 0 END) as '3位 人数'
,SUM(t.AMT) as '合計人数'
FROM (
    SELECT 
     p.PF_CODE
    ,p.PF_NAME
    ,f.AMT
    ,n.NATION_NAME
    ,RANK() OVER(PARTITION BY p.PF_CODE ORDER BY f.AMT desc, p.PF_CODE) RNK
    FROM FOREIGNER f
    INNER JOIN PREFECTURE p
        ON p.PF_CODE = f.PF_CODE
    INNER JOIN NATIONALITY n
        ON n.NATION_CODE = f.NATION_CODE
    WHERE n.NATION_CODE <> '113' AND n.NATION_NAME <> 'その他'
) t
GROUP BY
 t.PF_CODE
,t.PF_NAME
ORDER BY 合計人数 desc,1 asc