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
;