SELECT
    B.PF_CODE AS 都道府県コード,
    B.PF_NAME AS 都道府県名,
    MAX(CASE WHEN A.RANKING = 1 THEN C.NATION_NAME ELSE '' END) AS '1位 国名',
    MAX(CASE WHEN A.RANKING = 1 THEN A.AMT ELSE 0 END) AS '1位 人数',
    MAX(CASE WHEN A.RANKING = 2 THEN C.NATION_NAME ELSE '' END) AS '2位 国名',
    MAX(CASE WHEN A.RANKING = 2 THEN A.AMT ELSE 0 END) AS '2位 人数',
    MAX(CASE WHEN A.RANKING = 3 THEN C.NATION_NAME ELSE '' END) AS '3位 国名',
    MAX(CASE WHEN A.RANKING = 3 THEN A.AMT ELSE 0 END) AS '3位 人数',
    SUM(A.AMT) AS 合計人数
FROM (
    SELECT PF_CODE, NATION_CODE, AMT, ROW_NUMBER() OVER (
        PARTITION BY PF_CODE
        ORDER BY AMT DESC, NATION_CODE ASC
    ) AS RANKING
    FROM FOREIGNER
    WHERE NATION_CODE != '113'
) AS A
    LEFT OUTER JOIN PREFECTURE AS B ON A.PF_CODE = B.PF_CODE
    LEFT OUTER JOIN NATIONALITY AS C ON A.NATION_CODE = C.NATION_CODE
GROUP BY B.PF_CODE, B.PF_NAME
ORDER BY 合計人数 DESC, 都道府県コード ASC;