WITH T1 AS (
SELECT
    PF_CODE,
    NATION_CODE,
    AMT,
    RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC,PF_CODE) AS RANK
FROM FOREIGNER
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位 人数',
    SUM(AMT) 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 合計人数 DESC,都道府県コード