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