select
    p.pf_code as 都道府県コード	
,   p.pf_name as 都道府県名
,   max(case when vw.rank = 1 then n.nation_name else '' end) as "1位 国名"
,   sum(case when vw.rank = 1 then vw.amt else 0 end)         as "1位 人数"
,   max(case when vw.rank = 2 then n.nation_name else '' end) as "2位 国名"
,   sum(case when vw.rank = 2 then vw.amt else 0 end)         as "2位 人数"
,   max(case when vw.rank = 3 then n.nation_name else '' end) as "3位 国名"
,   sum(case when vw.rank = 3 then vw.amt else 0 end)         as "3位 人数"
,   sum(vw.amt) as 合計人数
from
(
    select
        f.*
    ,   row_number()over(partition by f.pf_code order by f.amt desc, f.nation_code) as rank
    from
        FOREIGNER f
    where
        f.nation_code != '113' -- その他
)   vw
inner join
    NATIONALITY n
on
    n.nation_code = vw.nation_code
inner join
    PREFECTURE p
on 
    p.pf_code = vw.pf_code
group by
    p.pf_code
,   p.pf_name
order by
    sum(vw.amt) desc
,   p.pf_code