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