WITH ranking AS(
    SELECT
        P.PF_CODE,
        P.PF_NAME,
        N.NATION_CODE,
        N.NATION_NAME,
        F.AMT,
        RANK() OVER (PARTITION BY P.PF_CODE ORDER BY F.AMT DESC) AS columnrank
    FROM
        FOREIGNER AS F
        INNER JOIN PREFECTURE AS P
        ON F.PF_CODE = P.PF_CODE
        INNER JOIN NATIONALITY AS N
        ON F.NATION_CODE = N.NATION_CODE
    WHERE
        N.NATION_CODE != '113'
)

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