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
            FR.PF_CODE,
            PF.PF_NAME,
            FR.NATION_CODE,
            NC.NATION_NAME,
            FR.AMT,
            RANK() OVER (
                PARTITION BY FR.PF_CODE
                ORDER BY
                    FR.AMT DESC,
                    FR.NATION_CODE ASC
            ) AS RANK
        FROM
            FOREIGNER AS FR
            INNER JOIN PREFECTURE AS PF ON PF.PF_CODE = FR.PF_CODE
            INNER JOIN NATIONALITY AS NC ON NC.NATION_CODE = FR.NATION_CODE
        WHERE
            FR.NATION_CODE != '113'
    ) AS SUB
GROUP BY
    SUB.PF_CODE
ORDER BY
    合計人数 DESC,
    SUB.PF_CODE ASC