with a as(
select (rank() OVER (PARTITION by p.PF_CODE   ORDER BY sum(f.AMT) DESC,p.PF_CODE)) AS ranking
,n.NATION_NAME as kuni
,f.AMT as kazu
,p.PF_CODE as code
,p.PF_NAME as ken
,sum(f.AMT) as sum
,n.NATION_CODE as ncode
from FOREIGNER as f
inner join NATIONALITY as n
on f.NATION_CODE=n.NATION_CODE
inner join  PREFECTURE as p
on p.PF_CODE=f.PF_CODE
where f.NATION_CODE!= '113' 
group by n.NATION_NAME,p.PF_NAME
order by
code,ranking
)
select code as 都道府県コード
,ken as 都道府県名
,max(case ranking 
when  1 then kuni else null end) as '1位 国名'
,max(case ranking 
when  1 then sum else 0 end) as '1位 人数'
,max(case ranking 
when  2 then kuni else null end) as '2位 国名'
,max(case ranking 
when  2 then sum else 0 end) as '2位 人数'
,max(case ranking 
when  3 then kuni else null end) as '3位 国名'
,max(case ranking 
when  3 then sum else 0 end) as '3位 人数'
, sum(sum) as 合計人数
from a
group by ken,code
order by
合計人数 desc,
都道府県コード,ncode