select
 f.PF_CODE as "都道府県コード"
,f.PF_NAME as "都道府県名"
,max(case when f.rnk = 1 then NATION_NAME else null end) as "1位 国名"
,max(case when f.rnk = 1 then AMT else null end) as "1位 人数"
,max(case when f.rnk = 2 then NATION_NAME else null end) as "2位 国名"
,max(case when f.rnk = 2 then AMT else null end) as "2位 人数"
,max(case when f.rnk = 3 then NATION_NAME else null end) as "3位 国名"
,max(case when f.rnk = 3 then AMT else null end) as "3位 人数"
,sum(AMT) as "合計人数"
from (select 
		 f.PF_CODE
		,p.PF_NAME
		,f.NATION_CODE
		,n.NATION_NAME
		,f.AMT
		,rank() over(partition by f.PF_CODE order by f.AMT desc,f.NATION_CODE asc) rnk
		from FOREIGNER f
		inner join NATIONALITY n
			on f.NATION_CODE = n.NATION_CODE
		inner join PREFECTURE p
			on f.PF_CODE = p.PF_CODE
		where f.NATION_CODE not in ('113')) f
group by 
 f.PF_CODE 
,f.PF_NAME 
order by 
 sum(AMT) desc