select
   s.pf_code as '都道府県コード'
  ,s.pf_name as '都道府県名'
  ,max(case rank_amt when 1 then s.nation_name end) as '1位 国名'
  ,sum(case rank_amt when 1 then s.amt else 0 end)  as '1位 人数'
  ,max(case rank_amt when 2 then s.nation_name end) as '2位 国名'
  ,sum(case rank_amt when 2 then s.amt else 0 end)  as '2位 人数'
  ,max(case rank_amt when 3 then s.nation_name end) as '3位 国名'
  ,sum(case rank_amt when 3 then s.amt else 0 end)  as '3位 人数'
  ,sum(s.amt) as '合計人数'
from (
  select
     t.pf_code 
    ,pf.pf_name
    ,t.nation_code
    ,nt.nation_name
    ,amt
    ,rank() over (
       partition by t.pf_code
       order by amt desc, t.nation_code
     ) as rank_amt
  from foreigner as t
  left outer join prefecture as pf
  on t.pf_code = pf.pf_code
  left outer join nationality as nt
  on t.nation_code = nt.nation_code
  where
    t.nation_code <> '113'
  ) as s
group by
   s.pf_code
  ,s.pf_name 
order by
   合計人数 desc
  ,都道府県コード