WITH
    t AS (
        SELECT
            main.PF_CODE PF_CODE
            ,main.NATION_CODE NATION_CODE
            ,nation.NATION_NAME
            ,RANK()OVER(PARTITION BY main.PF_CODE ORDER BY AMT DESC, main.NATION_CODE ASC) rank_wk
            ,main.AMT AMT
        FROM
            FOREIGNER main
        LEFT JOIN
            NATIONALITY nation
            ON main.NATION_CODE = nation.NATION_CODE
        WHERE
            main.NATION_CODE != '113'
        )
SELECT
    t.PF_CODE "都道府県コード"
    ,MAX(pref.PF_NAME) "都道府県名"
    ,MAX(CASE WHEN rank_wk = 1 THEN t.NATION_NAME ELSE NULL END) "1位 国名"
    ,MAX(CASE WHEN rank_wk = 1 THEN t.AMT ELSE NULL END) "1位 人数"
    ,MAX(CASE WHEN rank_wk = 2 THEN t.NATION_NAME ELSE NULL END) "2位 国名"
    ,MAX(CASE WHEN rank_wk = 2 THEN t.AMT ELSE NULL END) "2位 人数"
    ,MAX(CASE WHEN rank_wk = 3 THEN t.NATION_NAME ELSE NULL END) "3位 国名"
    ,MAX(CASE WHEN rank_wk = 3 THEN t.AMT ELSE NULL END) "3位 人数"
    ,SUM(t.AMT) "合計人数"
FROM
    t
LEFT JOIN
    PREFECTURE pref
    ON t.PF_CODE = pref.PF_CODE
GROUP BY
    t.PF_CODE
ORDER BY
    SUM(t.AMT) DESC
    ,t.PF_CODE ASC