SELECT
    SUB.PF_CODE AS 都道府県コード
    ,SUB.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 
        P.PF_CODE
        ,P.PF_NAME
        ,N.NATION_NAME
        ,AMT
        ,RANK () OVER(PARTITION BY P.PF_CODE ORDER BY AMT DESC) AS RANK
    FROM 
        FOREIGNER AS F
        INNER JOIN NATIONALITY AS N
            ON F.NATION_CODE = N.NATION_CODE
        INNER JOIN PREFECTURE P
            ON P.PF_CODE = F.PF_CODE
    WHERE
        N.NATION_CODE <> '113'
    ) AS SUB
GROUP BY PF_CODE, PF_NAME
ORDER BY 合計人数 DESC, 都道府県コード ASC;