SELECT
  DISTINCT
  MAX(PF_CODE) AS '都道府県コード'
  ,MAX(PF_NAME) AS '都道府県'
  ,MAX(CASE WHEN amt順 == 1 THEN NATION_NAME ELSE NULL END) AS '1位 国名'
  ,MAX(CASE WHEN amt順 == 1 THEN AMT ELSE NULL END) AS '1位 人数'
  ,MAX(CASE WHEN amt順 == 2 THEN NATION_NAME ELSE NULL END) AS '2位 国名'
  ,MAX(CASE WHEN amt順 == 2 THEN AMT ELSE NULL END) AS '2位 人数'
  ,MAX(CASE WHEN amt順 == 3 THEN NATION_NAME ELSE NULL END) AS '3位 国名'
  ,MAX(CASE WHEN amt順 == 3 THEN AMT ELSE NULL END) AS '3位 人数'
  ,SUM(AMT) AS '合計'
FROM(
SELECT
  T3.PF_NAME
  ,T1.PF_CODE
  ,T2.NATION_NAME
  ,AMT
  ,ROW_NUMBER() OVER(PARTITION BY T3.PF_CODE ORDER BY AMT DESC) AS 'amt順'
FROM
  FOREIGNER T1
  LEFT JOIN NATIONALITY T2 ON T1.NATION_CODE = T2.NATION_CODE
  LEFT JOIN PREFECTURE T3 ON T1.PF_CODE = T3.PF_CODE
WHERE
  T1.NATION_CODE <> '113'
)
GROUP BY
  PF_CODE
ORDER BY
  SUM(AMT) DESC
  ,PF_CODE ASC
;