WITH tmp AS (
SELECT
PF_CODE,
MAX(PF_NAME) AS PF_NAME,
MAX(NATION_NAME) AS NATION_NAME,
SUM(AMT) AS AMT,
RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS rk
FROM
(
SELECT
	f.PF_CODE,
	f.NATION_CODE,
	f.AMT,
	n.NATION_NAME,
	p.PF_NAME
FROM
	FOREIGNER f
LEFT JOIN
	NATIONALITY n
ON
	f.NATION_CODE = n.NATION_CODE
LEFT JOIN
	PREFECTURE p
ON
	f.PF_CODE = p.PF_CODE
WHERE
	f.NATION_CODE <> 113
)
GROUP BY
PF_CODE, NATION_CODE
)

SELECT
PF_CODE AS "都道府県コード",
PF_NAME AS "都道府県名",
MAX(CASE WHEN rk = 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名",
MAX(CASE WHEN rk = 1 THEN AMT ELSE NULL END) AS "1位 人数",
MAX(CASE WHEN rk = 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名",
MAX(CASE WHEN rk = 2 THEN AMT ELSE NULL END) AS "2位 人数",
MAX(CASE WHEN rk = 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名",
MAX(CASE WHEN rk = 3 THEN AMT ELSE NULL END) AS "3位 人数",
SUM(AMT) AS "合計人数"
FROM
tmp
GROUP BY
PF_CODE, PF_NAME
ORDER BY
"合計人数" DESC,
"都道府県コード" DESC