select
sub.PF_CODE as 都道府県コード
,sub.PF_NAME as 都道府県名
,MAX(case sub.RANK when 1 then sub.NATION_NAME else null end) as "1位 国名"
,MAX(case sub.RANK when 1 then sub.AMT  else 0 end) as "1位 人数"
,MAX(case sub.RANK when 2 then sub.NATION_NAME else null end) as "2位 国名"
,MAX(case sub.RANK when 2 then sub.AMT  else 0 end) as "2位 人数"
,MAX(case sub.RANK when 3 then sub.NATION_NAME else null end) as "3位 国名"
,MAX(case sub.RANK when 3 then sub.AMT  else 0 end) as "3位 人数"
,sum(sub.AMT) as "合計人数"
from
(select
 na.NATION_CODE
,na.NATION_NAME
,pr.PF_CODE
,pr.PF_NAME
,fo.AMT
,RANK () OVER (PARTITION BY pr.PF_CODE order by fo.AMT desc,fo.PF_CODE) as RANK
from FOREIGNER fo 
inner join NATIONALITY na on
 fo.NATION_CODE = na.NATION_CODE
 and na.NATION_CODE <> '113'
inner join PREFECTURE pr on
 fo.PF_CODE = pr.PF_CODE) sub
 group by sub.PF_CODE,sub.PF_NAME
order by 合計人数 desc,sub.PF_CODE asc