WITH TMP AS(
SELECT
    F.PF_CODE,
    P.PF_NAME,
    N.NATION_CODE,
    N.NATION_NAME,
    ROW_NUMBER()
        OVER(PARTITION BY F.PF_CODE ORDER BY F.AMT DESC,NATION_CODE ASC) AS RANK,
    AMT
FROM
    FOREIGNER AS F
JOIN
    PREFECTURE AS P
USING
    (PF_CODE)
JOIN
    NATIONALITY AS N
USING
    (NATION_CODE)
WHERE
    F.NATION_CODE != 113 
)

SELECT
    PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    MAX(CASE RANK WHEN 1 THEN NATION_NAME  ELSE NULL END) AS '1位 国名',
    MAX(CASE RANK WHEN 1 THEN AMT ELSE 0 END) AS '1位 人数',
    MAX(CASE RANK WHEN 2 THEN NATION_NAME  ELSE NULL END) AS '2位 国名',
    MAX(CASE RANK WHEN 2 THEN AMT ELSE 0 END) AS '2位 人数',
    MAX(CASE RANK WHEN 3 THEN NATION_NAME  ELSE NULL END) AS '3位 国名',
    MAX(CASE RANK WHEN 4 THEN AMT ELSE 0 END) AS '3位 人数',
    SUM(AMT) AS '合計人数'
FROM
    TMP
GROUP BY
    PF_CODE,
    PF_NAME
ORDER BY
    SUM(AMT) DESC,
    PF_CODE ASC