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