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