SELECT
	PF_CODE AS 都道府県コード
	, PF_NAME AS 都道府県名
	, group_concat(IIF(PRIORITY = 1,NATION_NAME, ''),'') AS "1位 国名"
	, SUM(IIF(PRIORITY = 1,AMT, 0)) AS "1位 人数"
	, group_concat(IIF(PRIORITY = 2,NATION_NAME, ''),'') AS "2位 国名"
	, SUM(IIF(PRIORITY = 2,AMT, 0)) AS "2位 人数"
	, group_concat(IIF(PRIORITY = 3,NATION_NAME, ''),'') AS "3位 国名"
	, SUM(IIF(PRIORITY = 3,AMT, 0)) AS "3位 人数"
	, SUM(AMT) AS "合計人数"
FROM
	(
SELECT
    PF_CODE
    , PF_NAME
    , ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS PRIORITY
    , NATION_CODE
    , NATION_NAME
    , AMT
FROM
    FOREIGNER
    INNER JOIN NATIONALITY
    USING (NATION_CODE)
    INNER JOIN PREFECTURE
    USING (PF_CODE)
WHERE
    NATION_CODE != 113
	) FX
GROUP BY
	PF_CODE, PF_NAME
ORDER BY
	SUM(AMT) DESC
	, PF_CODE