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