SELECT
    PFE.PF_CODE AS '都道府県コード'
    , PFE.PF_NAME AS '都道府県名'
    , MAX(CASE WHEN F_DATA.RANK_NUM = 1 THEN NAT.NATION_NAME END) AS '1位 国名'
    , MAX(CASE WHEN F_DATA.RANK_NUM = 1 THEN F_DATA.AMT END) AS '1位 人数'
    , MAX(CASE WHEN F_DATA.RANK_NUM = 2 THEN NAT.NATION_NAME END) AS '2位 国名'
    , MAX(CASE WHEN F_DATA.RANK_NUM = 2 THEN F_DATA.AMT END) AS '2位 人数'
    , MAX(CASE WHEN F_DATA.RANK_NUM = 3 THEN NAT.NATION_NAME END) AS '3位 国名'
    , MAX(CASE WHEN F_DATA.RANK_NUM = 4 THEN F_DATA.AMT END) AS '3位 人数'
    , SUM(FOR.AMT) AS '合計人数'
FROM
    FOREIGNER AS FOR
    INNER JOIN (    
        SELECT
            AMT
            , NATION_CODE
            , PF_CODE
            , RANK() OVER(
                PARTITION BY PF_CODE
                ORDER BY AMT DESC ,NATION_CODE
            ) RANK_NUM
        FROM
            FOREIGNER
        WHERE
            NATION_CODE != '113'
    ) AS F_DATA
        ON FOR.NATION_CODE = F_DATA.NATION_CODE
        AND FOR.PF_CODE = F_DATA.PF_CODE
        AND F_DATA.RANK_NUM <= 3
    INNER JOIN PREFECTURE AS PFE
        ON F_DATA.PF_CODE = PFE.PF_CODE
    INNER JOIN NATIONALITY NAT
        ON F_DATA.NATION_CODE = NAT.NATION_CODE
GROUP BY
    PFE.PF_CODE
ORDER BY
    合計人数 DESC
    , 都道府県コード ASC