SELECT
    sub.PF_CODE AS 都道府県コード
    , sub.PF_NAME AS 都道府県名
    , MAX(CASE sub.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
    , MAX(CASE sub.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
    , MAX(CASE sub.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
    , MAX(CASE sub.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
    , MAX(CASE sub.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
    , MAX(CASE sub.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
    , SUM(sub.AMT) AS 合計人数
FROM
    (
        SELECT
            f.PF_CODE
            ,p.PF_NAME
            ,f.NATION_CODE
            ,n.NATION_NAME
            ,f.AMT
            ,RANK() OVER (PARTITION BY f.PF_CODE ORDER BY f.AMT desc, f.PF_CODE desc) as rank
        FROM
            FOREIGNER f
        JOIN
            NATIONALITY n
        ON
            f.NATION_CODE = n.NATION_CODE
        JOIN
            PREFECTURE p
        ON
            p.PF_CODE = f.PF_CODE
        where
            f.NATION_CODE != '113'
    ) sub
GROUP BY
    sub.PF_CODE, sub.PF_NAME
ORDER BY
    合計人数 desc , 都道府県コード asc