WITH T1 AS (
SELECT
    FOR1.PF_CODE,
    NATION_CODE,
    AMT,
    RANK() OVER (PARTITION BY FOR1.PF_CODE ORDER BY AMT DESC,NATION_CODE) RANK,
    TOTAL
FROM FOREIGNER AS FOR1
INNER JOIN (SELECT
    PF_CODE,
    SUM(AMT) AS TOTAL
FROM FOREIGNER
GROUP BY PF_CODE
) AS FOR2 ON FOR1.PF_CODE=FOR2.PF_CODE
WHERE NATION_CODE<>'113'
)
SELECT
    PF.PF_CODE AS 都道府県コード,
    PF.PF_NAME AS 都道府県名,
    MAX(CASE RANK WHEN 1 THEN NATION_NAME ELSE 0 END) AS '1位 国名',
    MAX(CASE RANK WHEN 1 THEN AMT ELSE 0 END) AS '1位 人数',
    MAX(CASE RANK WHEN 2 THEN NATION_NAME ELSE 0 END) AS '2位 国名',
    MAX(CASE RANK WHEN 2 THEN AMT ELSE 0 END) AS '2位 人数',
    MAX(CASE RANK WHEN 3 THEN NATION_NAME ELSE 0 END) AS '3位 国名',
    MAX(CASE RANK WHEN 3 THEN AMT ELSE 0 END) AS '3位 人数',
    TOTAL AS 合計人数
FROM T1
INNER JOIN PREFECTURE AS PF ON PF.PF_CODE=T1.PF_CODE
INNER JOIN NATIONALITY AS NAT ON NAT.NATION_CODE=T1.NATION_CODE
GROUP BY PF.PF_CODE
ORDER BY TOTAL DESC,PF.PF_CODE