WITH R AS(
	SELECT
	 	P.PF_CODE,
	 	P.PF_NAME,
	 	N.NATION_CODE,
	 	N.NATION_NAME,
	 	F.AMT,
	 	RANK () OVER ( PARTITION BY P.PF_CODE ORDER BY F.AMT DESC, N.NATION_CODE ASC) AS "RANKING"
    FROM
		FOREIGNER F
		INNER JOIN
		PREFECTURE P
		ON
			F.PF_CODE = P.PF_CODE
		INNER JOIN
		NATIONALITY N
		ON
			F.NATION_CODE = N.NATION_CODE
    WHERE
    	F.NATION_CODE<>'113'
)
SELECT
 	PF_CODE AS "都道府県コード",
 	PF_NAME AS "都道府県名",
	MAX(CASE WHEN RANKING=1 THEN NATION_NAME ELSE 0 END) AS "1位 国名",
	SUM(CASE WHEN RANKING=1 THEN AMT ELSE 0 END) AS "1位 人数",
	MAX(CASE WHEN RANKING=2 THEN NATION_NAME ELSE 0 END) AS "2位 国名",
	SUM(CASE WHEN RANKING=2 THEN AMT ELSE 0 END) AS "2位 人数",
	MAX(CASE WHEN RANKING=3 THEN NATION_NAME ELSE 0 END) AS "3位 国名",
	SUM(CASE WHEN RANKING=3 THEN AMT ELSE 0 END) AS "3位 人数",
	SUM(AMT) AS "合計人数"
FROM
	R
GROUP BY
	PF_CODE, PF_NAME
ORDER BY
    9 DESC, 1 ASC