WITH Table1 AS (
SELECT PF_CODE,NAT.NATION_CODE,NAT.NATION_NAME,AMT, ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC,NAT.NATION_CODE) AS 順位
FROM FOREIGNER AS FOR INNER JOIN NATIONALITY AS NAT ON FOR.NATION_CODE=NAT.NATION_CODE
WHERE NAT.NATION_CODE<>113
ORDER BY PF_CODE
)
SELECT PF.PF_CODE 都道府県コード,PF.PF_NAME 都道府県名,R1.NATION_NAME '1位 国名',R1.AMT '1位 人数',
R2.NATION_NAME '2位 国名',R2.AMT '2位 人数',
R3.NATION_NAME '3位 国名',R3.AMT '3位 人数',
R1.AMT+R2.AMT+R3.AMT 合計人数
FROM Table1 AS R1 
INNER JOIN PREFECTURE AS PF ON R1.PF_CODE=PF.PF_CODE
INNER JOIN Table1 AS R2 ON R1.PF_CODE=R2.PF_CODE AND R2.順位=2
INNER JOIN Table1 AS R3 ON R1.PF_CODE=R3.PF_CODE AND R3.順位=3
WHERE R1.順位=1
ORDER BY 合計人数 DESC,PF.PF_CODE