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