SELECT
    SUB.PF_CODE AS 都道府県コード
    ,SUB.PF_NAME AS 都道府県名
    ,MAX(CASE WHEN SUB.RANK = 1 THEN NATION_NAME END) AS '1位 国名'
    ,MAX(CASE WHEN SUB.RANK = 1 THEN AMT END) AS '1位 人数'
    ,MAX(CASE WHEN SUB.RANK = 2 THEN NATION_NAME END) AS '2位 国名'
    ,MAX(CASE WHEN SUB.RANK = 2 THEN AMT END) AS '2位 人数'
    ,MAX(CASE WHEN SUB.RANK = 3 THEN NATION_NAME END) AS '3位 国名'
    ,MAX(CASE WHEN SUB.RANK = 3 THEN AMT 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
ORDER BY SUM(AMT) DESC, PF_CODE ASC