SELECT
  DISTINCT
  MAX(PF_CODE)
  ,MAX(PF_NAME)
  ,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
;