with base AS (
    SELECT
      fore.*
      , pref.PF_NAME
      , nati.NATION_NAME
      , RANK() OVER (PARTITION BY pref.PF_NAME ORDER BY fore.AMT DESC) as rnk
    FROM
      FOREIGNER fore
      
      INNER JOIN PREFECTURE pref ON fore.PF_CODE = pref.PF_CODE
      INNER JOIN NATIONALITY nati ON fore.NATION_CODE = nati.NATION_CODE
      
    WHERE
      fore.NATION_CODE != 113
    )

SELECT
  PF_CODE AS 都道府県コード
  , PF_NAME AS 都道府県名
  , MAX(CASE WHEN rnk == 1 THEN NATION_NAME END) AS "1位 国名"
  , MAX(CASE WHEN rnk == 1 THEN AMT END) AS "1位 人数"
  , MAX(CASE WHEN rnk == 2 THEN NATION_NAME END) AS "2位 国名"
  , MAX(CASE WHEN rnk == 2 THEN AMT END) AS "2位 人数"
  , MAX(CASE WHEN rnk == 3 THEN NATION_NAME END) AS "3位 国名"
  , MAX(CASE WHEN rnk == 3 THEN AMT END) AS "3位 人数"
  , SUM(AMT) AS 合計人数
FROM
  base
GROUP BY
  PF_CODE
ORDER BY
  合計人数 DESC, 都道府県コード