SELECT
  WK1.'都道府県コード' AS '都道府県コード'
  , PF.PF_NAME AS '都道府県名'
  , MAX(case when WK1.RANK = 1 then WK1.'国名' end) AS '1位 国名'
  , SUM(case when WK1.RANK = 1 then WK1.AMT end) AS '1位 人数'
  , MAX(case when WK1.RANK = 2 then WK1.'国名' end) AS '2位 国名'
  , SUM(case when WK1.RANK = 2 then WK1.AMT end) AS '2位 人数'
  , MAX(case when WK1.RANK = 3 then WK1.'国名' end) AS '3位 国名'
  , SUM(case when WK1.RANK = 3 then WK1.AMT end) AS '3位 人数'
  , SUM(WK1.AMT) AS '合計人数'
FROM
(
  SELECT 
    FN.PF_CODE AS '都道府県コード'
    , FN.NATION_CODE AS '国籍コード'
    , NT.NATION_NAME AS '国名'
    , ROW_NUMBER() over(partition by FN.PF_CODE order by AMT DESC, PF_CODE ASC) AS 'RANK'
    , AMT
  FROM FOREIGNER FN
  INNER JOIN NATIONALITY NT ON NT.NATION_CODE = FN.NATION_CODE
  WHERE FN.NATION_CODE <> '113'
  ORDER BY RANK() over(order by  AMT DESC, PF_CODE ASC)
) WK1
INNER JOIN PREFECTURE PF ON WK1.'都道府県コード' = PF.PF_CODE
GROUP BY WK1.'都道府県コード'
ORDER BY SUM(WK1.AMT) DESC, WK1.'都道府県コード'