SELECT
	都道府県コード
	,都道府県名
	,MAX(CASE WHEN rn = 1 THEN 国名 ELSE NULL END) as "1位 国名"
	,MAX(CASE WHEN rn = 1 THEN AMT ELSE NULL END) as "1位 人数"
	,MAX(CASE WHEN rn = 2 THEN 国名 ELSE NULL END) as "2位 国名"
	,MAX(CASE WHEN rn = 2 THEN AMT ELSE NULL END) as "2位 人数"
	,MAX(CASE WHEN rn = 3 THEN 国名 ELSE NULL END) as "3位 国名"
	,MAX(CASE WHEN rn = 3 THEN AMT ELSE NULL END) as "3位 人数"
	,SUM(AMT) as 合計人数
FROM
	(
		SELECT
			f.PF_CODE as 都道府県コード
			,p.PF_NAME  as 都道府県名
			,n.NATION_NAME as 国名
			,AMT
			,ROW_NUMBER() OVER (PARTITION BY f.PF_CODE ORDER BY AMT DESC) as rn
		FROM
			FOREIGNER as f
		INNER JOIN
			PREFECTURE as p
		ON
			f.PF_CODE = p.PF_CODE
		INNER JOIN
			NATIONALITY as n
		ON
			f.NATION_CODE = n.NATION_CODE
		WHERE
			n.NATION_CODE <> 113		
	)
GROUP BY
	都道府県コード
	,都道府県名
ORDER BY
	合計人数 DESC
	,都道府県コード