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