ALTER TABLE NATIONALITY ADD RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS AMT_RANK NVARCHAR(100);
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, 都道府県コード;