WITH SUBRENBAN AS (
    SELECT
        T1.PF_CODE
      , M1.PF_NAME
      , T1.NATION_CODE
      , M2.NATION_NAME
      , T1.AMT
      , SUM(CASE WHEN T1.AMT < T2.AMT OR (T1.AMT = T2.AMT AND T1.NATION_CODE < T2.NATION_CODE) THEN 1 ELSE 0 END) AS RENBAN
    FROM
        FOREIGNER T1
        LEFT OUTER JOIN PREFECTURE M1
            ON  T1.PF_CODE = M1.PF_CODE
        LEFT OUTER JOIN NATIONALITY M2
            ON  T1.NATION_CODE = M2.NATION_CODE
        LEFT OUTER JOIN FOREIGNER T2
            ON  T1.PF_CODE = T2.PF_CODE
    WHERE
          T1.NATION_CODE <> '113'
      AND T2.NATION_CODE <> '113'
    GROUP BY
        T1.PF_CODE
      , M1.PF_NAME
      , T1.NATION_CODE
      , M2.NATION_NAME
      , T1.AMT
)
SELECT
    T1.PF_CODE                                                   AS "都道府県コード"
  , T1.PF_NAME                                                   AS "都道府県名"
  , MAX(CASE WHEN T1.RENBAN = 0 THEN T1.NATION_NAME ELSE '' END) AS "1位 国名"
  , SUM(CASE WHEN T1.RENBAN = 0 THEN T1.AMT         ELSE 0  END) AS "1位 人数"
  , MAX(CASE WHEN T1.RENBAN = 1 THEN T1.NATION_NAME ELSE '' END) AS "2位 国名"
  , SUM(CASE WHEN T1.RENBAN = 1 THEN T1.AMT         ELSE 0  END) AS "2位 人数"
  , MAX(CASE WHEN T1.RENBAN = 2 THEN T1.NATION_NAME ELSE '' END) AS "3位 国名"
  , SUM(CASE WHEN T1.RENBAN = 2 THEN T1.AMT         ELSE 0  END) AS "3位 人数"
  , SUM(T1.AMT)                                                  AS "合計人数"
FROM
    SUBRENBAN T1
GROUP BY
    T1.PF_CODE
ORDER BY
    合計人数 DESC
  , 都道府県コード
;