SELECT
    PF_CODE AS 都道府県コード, 
    PF_NAME AS 都道府県名, 
    MAX(CASE SUB.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名", 
    MAX(CASE SUB.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数", 
    MAX(CASE SUB.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名", 
    MAX(CASE SUB.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数", 
    MAX(CASE SUB.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名", 
    MAX(CASE SUB.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数", 
    SUM(AMT) AS 合計人数 
FROM
    ( 
        SELECT
            F.PF_CODE AS PF_CODE, 
            P.PF_NAME AS PF_NAME, 
            N.NATION_NAME AS NATION_NAME, 
            F.AMT AS AMT, 
            RANK() OVER ( 
                PARTITION BY
                    F.PF_CODE 
                ORDER BY
                    AMT DESC, 
                    F.NATION_CODE
            ) AS RANK                           
        FROM
            FOREIGNER AS F 
            INNER JOIN NATIONALITY AS N 
            ON N.NATION_CODE = F.NATION_CODE 
            INNER JOIN PREFECTURE AS P 
            ON P.PF_CODE = F.PF_CODE 
        WHERE
            F.NATION_CODE != '113'
    ) AS SUB 
GROUP BY
    SUB.PF_CODE, 
    SUB.PF_NAME
ORDER BY
    合計人数 DESC, 
    都道府県コード ASC;