ALTER TABLE NATIONALITY ADD RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS AMT_RANK INTEGER;

SELECT PR.PF_CODE 都道府県コード, PR.PF_NAME 都道府県名,
    CASE WHEN AMT_RANK = 1 THEN NA.NATION_NAME ELSE NULL END AS "1位 国名",
    CASE WHEN AMT_RANK = 1 THEN FO.AMT ELSE 0 END AS "1位 人数",
    CASE WHEN AMT_RANK = 2 THEN NA.NATION_NAME ELSE NULL END AS "2位 国名",
    CASE WHEN AMT_RANK = 2 THEN FO.AMT ELSE 0 END AS "2位 人数",
    CASE WHEN AMT_RANK = 3 THEN NA.NATION_NAME ELSE NULL END AS "3位 国名",
    CASE WHEN AMT_RANK = 3 THEN FO.AMT ELSE 0 END AS "3位 人数",
    SUM(NA.AMT) 合計人数
FROM FOREIGNER FO
    INNER JOIN NATIONALITY NA ON FO.NATION_CODE = NA.NATION_CODE
    INNER JOIN PREFECTURE PR ON FO.PF_CODE = PR.PF_CODE
WHERE NA.NATION_CODE != "113"
GROUP BY 都道府県コード, 都道府県名
ORDER BY 合計人数 DESC, 都道府県コード;