SELECT
    PF_CODE AS "都道府県コード",
    PF_NAME AS "都道府県名",
    MAX(CASE WHEN rank = 1 THEN NATION_NAME END) AS "1位 国名",
    SUM(CASE WHEN rank = 1 THEN sum_amt END) AS "1位 人数",
    MAX(CASE WHEN rank = 2 THEN NATION_NAME END) AS "2位 国名",
    SUM(CASE WHEN rank = 2 THEN sum_amt END) AS "2位 人数",
    MAX(CASE WHEN rank = 3 THEN NATION_NAME END) AS "3位 国名",
    SUM(CASE WHEN rank = 3 THEN sum_amt END) AS "3位 人数",
    SUM(sum_amt) AS "合計人数"
FROM
(
    SELECT
        PF_CODE,
        PF_NAME,
        NATION_CODE,
        NATION_NAME,
        RANK() OVER(PARTITION BY PF_CODE ORDER BY sum_amt DESC, NATION_CODE ASC) AS rank,
        sum_amt
    FROM
    (
        SELECT
            p.PF_CODE,
            p.PF_NAME,
            n.NATION_CODE,
            n.NATION_NAME,
            SUM(f.AMT) sum_amt
        FROM
            FOREIGNER AS f
            INNER JOIN PREFECTURE AS p
                ON p.PF_CODE = f.PF_CODE
            INNER JOIN NATIONALITY AS n
                ON n.NATION_CODE = f.NATION_CODE
        WHERE
            n.NATION_CODE <> "113"
        GROUP BY
            1,2,3,4
    )
)
GROUP BY
    1,2
ORDER BY
    SUM(sum_amt) DESC,
    PF_CODE ASC