WITH TMP AS (

SELECT
    FOR.PF_CODE AS 都道府県コード,
    PRE.PF_NAME AS 都道府県名,
    NAT.NATION_NAME AS NATION_NAME,
    FOR.AMT AS AMT,
    RANK() OVER (PARTITION BY FOR.PF_CODE ORDER BY AMT DESC, FOR.NATION_CODE) AS "RK"
FROM
    FOREIGNER AS FOR
INNER JOIN NATIONALITY AS NAT
    ON FOR.NATION_CODE = NAT.NATION_CODE
INNER JOIN PREFECTURE AS PRE
    ON FOR.PF_CODE = PRE.PF_CODE
WHERE
    FOR.NATION_CODE != "113"

ORDER BY
    FOR.PF_CODE,
    FOR.AMT DESC
)
SELECT
    都道府県コード,
    都道府県名,
    MAX(CASE WHEN RK = 1 THEN NATION_NAME ELSE "" END) AS "1位 国名",
    MAX(CASE WHEN RK = 1 THEN AMT ELSE 0 END) AS "1位 人数",
    MAX(CASE WHEN RK = 2 THEN NATION_NAME ELSE "" END) AS "2位 国名",
    MAX(CASE WHEN RK = 2 THEN AMT ELSE 0 END) AS "2位 人数",
    MAX(CASE WHEN RK = 3 THEN NATION_NAME ELSE "" END) AS "3位 国名",
    MAX(CASE WHEN RK = 3 THEN AMT ELSE 0 END) AS "3位 人数",
    SUM(AMT) AS 合計人数
FROM
    TMP
GROUP BY
    都道府県コード
ORDER BY
    SUM(AMT) DESC,
    都道府県コード