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