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