WITH RANKING AS (
SELECT
	P.PF_CODE,
	P.PF_NAME,
	N.NATION_NAME,
	F.AMT,
	RANK() over (PARTITION BY P.PF_CODE ORDER BY F.AMT DESC, F.NATION_CODE) as RANK
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
	AND N.NATION_CODE <> '113'
)

SELECT
    PF_CODE AS 都道府県コード,
    max(PF_NAME) AS 都道府県名,
    max(case when RANK = 1 then NATION_NAME else NULL end) as '1位 国名',
    max(case when RANK = 1 then AMT else NULL end) as '1位 人数',
    max(case when RANK = 2 then NATION_NAME else NULL end) as '2位 国名',
    max(case when RANK = 2 then AMT else NULL end) as '2位 人数',
    max(case when RANK = 3 then NATION_NAME else NULL end) as '3位 国名',
    max(case when RANK = 3 then AMT else NULL end) as '3位 人数',
    sum(AMT) as '合計人数'
FROM
    RANKING
GROUP BY
    PF_CODE
ORDER BY
    sum(AMT) desc,
    PF_CODE asc