SELECT 
 SUB.PF_CODE AS 都道府県コード
 , SUB.PF_NAME AS 都道府県名
 , MAX(CASE SUB.RANK WHEN 1 THEN SUB.NATION_NAME ELSE NULL END) AS "1位 国名"
 , MAX(CASE SUB.RANK WHEN 1 THEN SUB.AMT ELSE 0 END) AS "1位 人数"
 , MAX(CASE SUB.RANK WHEN 2 THEN SUB.NATION_NAME ELSE NULL END) AS "2位 国名"
 , MAX(CASE SUB.RANK WHEN 2 THEN SUB.AMT ELSE 0 END) AS "2位 人数"
 , MAX(CASE SUB.RANK WHEN 3 THEN SUB.NATION_NAME ELSE NULL END) AS "3位 国名"
 , MAX(CASE SUB.RANK WHEN 3 THEN SUB.AMT ELSE 0 END) AS "3位 人数"
 , SUM(SUB.AMT) AS 合計人数
FROM
(SELECT FOR.PF_CODE, PER.PF_NAME, NAT.NATION_NAME, FOR.AMT, RANK() OVER (PARTITION BY FOR.PF_CODE ORDER BY FOR.AMT DESC, FOR.NATION_CODE ) AS RANK 
FROM FOREIGNER FOR
INNER JOIN NATIONALITY NAT
ON FOR.NATION_CODE = NAT.NATION_CODE
INNER JOIN PREFECTURE PER
ON PER.PF_CODE = FOR.PF_CODE
WHERE FOR.NATION_CODE != '113') SUB
GROUP BY SUB.PF_CODE, SUB.PF_NAME
ORDER BY 合計人数 DESC, 都道府県コード ASC