select
  X.PF_CODE as 都道府県コード,
  P.PF_NAME as 都道府県名,
  MAX(case when X.rank=1 then X.NATION_NAME END) as "1位 国名",
  MAX(case when X.rank=1 then X.AMT END) as "1位 人数",
  MAX(case when X.rank=2 then X.NATION_NAME END) as "2位 国名",
  MAX(case when X.rank=2 then X.AMT END) as "2位 人数",
  MAX(case when X.rank=3 then X.NATION_NAME END) as "3位 国名",
  MAX(case when X.rank=3 then X.AMT END) as "3位 人数",
  SUM(X.AMT) as "合計人数"
from
(select
  F.PF_CODE,
  F.NATION_CODE,
  N.NATION_NAME,
  F.AMT,
  rank() over(partition by PF_CODE order by AMT DESC, F.NATION_CODE) as rank
from 
  FOREIGNER F
join
  NATIONALITY N
on
  F.NATION_CODE = N.NATION_CODE
where
  N.NATION_CODE <> 113) X
join
  PREFECTURE P
on
  X.PF_CODE = P.PF_CODE
group by
  X.PF_CODE
order by
  9 DESC, 1;