WITH MY_DATA AS(
SELECT
 P.PF_CODE AS "都道府県コード",
 P.PF_NAME AS "都道府県名",
 N.NATION_NAME AS "国名",
 F.AMT AS "人数",
 ROW_NUMBER() OVER(PARTITION BY P.PF_CODE ORDER BY F.AMT DESC) AS rnk
FROM FOREIGNER F
INNER JOIN NATIONALITY N ON F.NATION_CODE = N.NATION_CODE
INNER JOIN PREFECTURE P ON F.PF_CODE = P.PF_CODE
WHERE F.NATION_CODE IS NOT "113")

SELECT
 都道府県コード, 都道府県名,
 MAX(CASE WHEN rnk=1 THEN 国名 END) AS "1位 国名",
 MAX(CASE WHEN rnk=1 THEN 人数 END) AS "1位 人数",
 MAX(CASE WHEN rnk=2 THEN 国名 END) AS "2位 国名",
 MAX(CASE WHEN rnk=2 THEN 人数 END) AS "2位 人数",
 MAX(CASE WHEN rnk=3 THEN 国名 END) AS "3位 国名",
 MAX(CASE WHEN rnk=3 THEN 人数 END) AS "3位 人数",
 SUM(人数) AS "合計人数"
FROM MY_DATA
GROUP BY 1
ORDER BY 合計人数 DESC, 都道府県コード