select
 FR.PF_CODE as 都道府県コード
 ,PREFECTURE.PF_NAME as 都道府県名
 ,(case when FR.rn=1 then NATIONALITY.NATION_NAME else '' end) as "1位 国名"
 ,(case when FR.rn=1 then FR.AMT else '' end) as "1位 人数"
 ,(case when FR.rn=2 then NATIONALITY.NATION_NAME else '' end) as "2位 国名"
 ,(case when FR.rn=2 then FR.AMT else '' end) as "2位 人数"
 ,(case when FR.rn=3 then NATIONALITY.NATION_NAME else '' end) as "3位 国名"
 ,(case when FR.rn=3 then FR.AMT else '' end) as "3位 人数"
 ,sum(FR.AMT) as 合計人数
from
 (
 select *, ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT desc) rn from FOREIGNER) as FR
 inner join PREFECTURE on PREFECTURE.PF_CODE = FR.PF_CODE
 inner join NATIONALITY on NATIONALITY.NATION_CODE = FR.NATION_CODE
where
 FR.rn<=3
group by
 FR.PF_CODE
order by
 合計人数 desc
 ,都道府県コード