WITH Pre AS(
SELECT
    F.PF_CODE AS 都道府県コード,
    P.PF_NAME AS 都道府県名,
    N.NATION_NAME AS NATION_NAME,
    RANK() OVER(PARTITION BY F.PF_CODE ORDER BY AMT DESC) AS rank,
    AMT
FROM
    FOREIGNER AS F
JOIN
    NATIONALITY AS N
ON
    F.NATION_CODE = N.NATION_CODE
JOIN
    PREFECTURE AS P
ON
    F.PF_CODE = P.PF_CODE
WHERE
    N.NATION_CODE <> "113"
)
SELECT
    都道府県コード,
    都道府県名,
    MAX(CASE WHEN RANK = 1 THEN NATION_NAME END) AS "1位 国名",
    SUM(CASE WHEN RANK = 1 THEN AMT END) AS "1位 人数",
    MAX(CASE WHEN RANK = 2 THEN NATION_NAME END) AS "2位 国名",
    SUM(CASE WHEN RANK = 2 THEN AMT END) AS "2位 人数",
    MAX(CASE WHEN RANK = 3 THEN NATION_NAME END) AS "3位 国名",
    SUM(CASE WHEN RANK = 3 THEN AMT END) AS "3位 人数",
    SUM(AMT) AS 合計人数
FROM
    Pre
GROUP BY
    都道府県コード
ORDER BY
    合計人数 desc,
    都道府県コード asc