with s as (select 
pf_code
,nation_code
,amt
,row_number() OVER(PARTITION BY pf_code ORDER BY amt DESC,pf_code) AS rnk

from FOREIGNER

where nation_code != 113
)

select 
s.PF_CODE 都道府県コード
,PF_NAME 都道府県名
,max(case when rnk=1 then NATION_NAME else null end) [1位 国名]
,max(case when rnk=1 then amt else null end) [1位 人数]
,max(case when rnk=2 then NATION_NAME else null end) [2位 国名]
,max(case when rnk=2 then amt else null end) [2位 人数]
,max(case when rnk=3 then NATION_NAME else null end) [3位 国名]
,max(case when rnk=3 then amt else null end) [3位 人数]
,sum(amt) 合計人数

from s

inner join PREFECTURE pref
on pref.pf_code = s.pf_code

inner join NATIONALITY nat
on nat.nation_code = s.nation_code

group by s.PF_CODE
order by 合計人数 desc, s.pf_code