select
 a.PF_CODE as 都道府県コード
,a.PF_NAME as 都道府県名
,max(case a.rank when 1 then a.NATION_NAME end) as '1位 国名'
,max(case a.rank when 1 then a.AMT end) as '1位 人数'
,max(case a.rank when 2 then a.NATION_NAME end) as '2位 国名'
,max(case a.rank when 2 then a.AMT end) as '2位 人数'
,max(case a.rank when 3 then a.NATION_NAME end) as '3位 国名'
,max(case a.rank when 3 then a.AMT end) as '3位 人数'
,sum(AMT) as 合計人数
from (
select
 a.PF_CODE
,b.PF_NAME
,a.NATION_CODE
,c.NATION_NAME
,a.AMT
,ROW_NUMBER()OVER(PARTITION BY a.PF_CODE ORDER BY a.AMT desc) rank
from FOREIGNER a
inner join PREFECTURE b
 on a.PF_CODE = b.PF_CODE
inner join NATIONALITY c
 on a.NATION_CODE = c.NATION_CODE
where 1=1
and a.NATION_CODE <> '113'
) a
group by a.PF_CODE
order by 合計人数 desc, a.PF_CODE asc