select 
 x.PF_CODE as 都道府県コード,
 x.PF_NAME as 都道府県名,
 max(case when ranking = 1 then x.ddd else null end) as '1位 国名',
 sum(case when ranking = 1 then x.amt else 0 end)  as '1位 人数',
 max(case when ranking = 2 then x.ddd else null end)  as '2位 国名',
 sum(case when ranking = 2 then x.amt else 0 end)  as '2位 人数',
 max(case when ranking = 3 then x.ddd else null end) as '3位 国名',
 sum(case when ranking = 3 then x.amt else 0 end)  as '3位 人数',
 sum(amt) as 合計人数
from 
( 
select 
 f.PF_CODE,
 f.NATION_CODE,
 f.AMT,
 n.NATION_NAME as ddd,
 p.PF_NAME, 
 rank() over(partition by f.pf_code order by  f.amt desc, f.nation_code asc) as ranking
from 
 FOREIGNER as f
inner join
 NATIONALITY as n on f.NATION_CODE = n.NATION_CODE and n.NATION_CODE <> '113'
inner join
 PREFECTURE as p on f.PF_CODE = p.PF_CODE
) as x

group by 
 x.pf_name, x.pf_code
order by
 合計人数 desc,
 都道府県コード asc
 
;