with subq as 
(
select A.PF_CODE
	,A.NATION_CODE
	,AMT
	,NATION_NAME
	,PF_NAME
	,row_number() over (partition by A.PF_CODE order by AMT desc)  as rank
	
from FOREIGNER A
left outer join NATIONALITY B
on A.NATION_CODE = B.NATION_CODE
left outer join PREFECTURE C
on A.PF_CODE = C.PF_CODE
)

select PF_CODE as 都道府県コード
	,PF_NAME as 都道府県名
	,max(case when rank = 1 then NATION_NAME else null end) as [1位 国名]
	,sum(case when rank = 1 then AMT else 0 end) as [1位 人数]
	,max(case when rank = 2 then NATION_NAME else null end) as [2位 国名]
	,sum(case when rank = 2 then AMT else 0 end) as [2位 人数]
	,max(case when rank = 3 then NATION_NAME else null end) as [3位 国名]
	,sum(case when rank = 3 then AMT else 0 end) as [3位 人数]
	,sum(AMT) as 合計人数
from subq
group by PF_CODE,PF_NAME
order by 合計人数 desc,都道府県コード asc