SELECT
  f.PF_CODE AS '都道府県コード',
  PF_NAME AS '都道府県名',
  (CASE WHEN rank = 1 THEN NATION_NAME ELSE null END) AS '1位国名',
  SUM(CASE WHEN rank = 1 THEN AMT ELSE 0 END) AS '1位人数',
  (CASE WHEN rank = 2 THEN NATION_NAME ELSE null END) AS '2位国名',
  SUM(CASE WHEN rank = 2 THEN AMT ELSE 0 END) AS '2位人数',
  (CASE WHEN rank = 3 THEN NATION_NAME ELSE null END) AS '3位国名',
  SUM(CASE WHEN rank = 3 THEN AMT ELSE 0 END) AS '3位人数',
  SUM(sum) as '合計人数'
FROM (
  SELECT
    PF_CODE,
    f.NATION_CODE,
    n.NATION_NAME,
    AMT,
    RANK () OVER (
      PARTITION BY PF_CODE
      ORDER BY AMT DESC
    ) as rank,
    SUM(AMT) AS sum
  FROM
    FOREIGNER AS f
    inner JOIN NATIONALITY AS n
    ON f.NATION_CODE = n.NATION_CODE
  WHERE
    f.NATION_CODE <> 113
  GROUP BY PF_CODE, f.NATION_CODE
) as f
  inner JOIN PREFECTURE as p
  ON f.PF_CODE = p.PF_CODE
GROUP BY f.PF_CODE
ORDER BY SUM(sum) DESC, f.PF_CODE