WITH subq AS (
--各テーブルの結合、条件外のデータ除去、都道府県ごとの順位付け
    SELECT
    F.PF_CODE AS PF_CODE
    ,P.PF_NAME AS PF_NAME
    ,F.NATION_CODE
    ,N.NATION_NAME AS NATION_NAME
    ,F.AMT AS AMT
    ,RANK () OVER(PARTITION BY F.PF_CODE ORDER BY F.AMT DESC) AS RANK
FROM
    FOREIGNER AS F
LEFT OUTER JOIN
    PREFECTURE AS P
  ON
    F.PF_CODE = P.PF_CODE
LEFT OUTER JOIN
    NATIONALITY AS N
  ON
    F.NATION_CODE = N.NATION_CODE
WHERE
    F.NATION_CODE <> '113'
    )

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