SELECT
    A.PF_CODE AS 都道府県コード
    ,C.PF_NAME AS 都道府県名
    ,MAX(CASE WHEN A.RANKS = '1' THEN B.NATION_NAME END) AS "1位 国名"
    ,MAX(CASE WHEN A.RANKS = '1' THEN A.AMT END) AS "1位 人数"
    ,MAX(CASE WHEN A.RANKS = '2' THEN B.NATION_NAME  END) AS "2位 国名"
    ,MAX(CASE WHEN A.RANKS = '2' THEN A.AMT END) AS "2位 人数"
    ,MAX(CASE WHEN A.RANKS = '3' THEN B.NATION_NAME END) AS "3位 国名"
    ,MAX(CASE WHEN A.RANKS = '3' THEN A.AMT END) AS "3位 人数"
    ,SUM(A.AMT) AS 合計人数
FROM
    (
        SELECT
             PF_CODE
            ,RANK()OVER(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