WITH TMP AS(
SELECT
    F.PF_CODE
    ,P.PF_NAME
    ,F.NATION_CODE
    ,N.NATION_NAME
    ,F.AMT AS TOTAL
    ,RANK()
        OVER(PARTITION BY F.PF_CODE ORDER BY F.AMT DESC,F.PF_CODE DESC) AS RANK
FROM
    FOREIGNER AS F
JOIN
    PREFECTURE AS P
ON
    F.PF_CODE = P.PF_CODE
JOIN
    NATIONALITY AS N
ON
    F.NATION_CODE = N.NATION_CODE
WHERE
    F.NATION_CODE != 113
ORDER BY
    F.PF_CODE ASC
    ,TOTAL DESC
)

SELECT
    PF_CODE AS '都道府県コード'
    ,PF_NAME AS '都道府県名'
    ,MAX(CASE WHEN RANK = 1 THEN NATION_NAME ELSE 0 END) AS '1位 国名'
    ,MAX(CASE WHEN RANK = 1 THEN TOTAL ELSE 0 END) AS '1位 人数'
    ,MAX(CASE WHEN RANK = 2 THEN NATION_NAME ELSE 0 END) AS '2位 国名'
    ,MAX(CASE WHEN RANK = 2 THEN TOTAL ELSE 0 END) AS '2位 人数'
    ,MAX(CASE WHEN RANK = 3 THEN NATION_NAME ELSE 0 END) AS '3位 国名'
    ,MAX(CASE WHEN RANK = 3 THEN TOTAL ELSE 0 END) AS '3位 人数'
    ,SUM(TOTAL) AS '合計人数'
FROM
    TMP
GROUP BY
    PF_CODE
    ,PF_NAME
ORDER BY
    SUM(TOTAL) DESC
    ,PF_CODE ASC;