with tmp1 as(
select 
    *
    ,RANK() OVER (PARTITION BY f.pf_code ORDER BY amt DESC, f.nation_code) AS RankNo
from FOREIGNER f 
left join NATIONALITY n on f.NATION_CODE=n.NATION_CODE
left join PREFECTURE p on f.pf_code=p.pf_code
order by 1,3 desc
)
-- select * from tmp1;
-- select sum(amt) from tmp1 where pf_code="11000"

select
    pf_code as "都道府県コード"
    ,pf_name as "都道府県名"
    ,max(case when RankNo=1 then nation_name else null end) as "1位 国名"
    ,sum(case when RankNo=1 then amt else 0 end) as "1位 人数"
    ,max(case when RankNo=2 then nation_name else null end) as "2位 国名"
    ,sum(case when RankNo=2 then amt else 0 end) as "2位 人数"
    ,max(case when RankNo=3 then nation_name else null end) as "3位 国名"
    ,sum(case when RankNo=3 then amt else 0 end) as "3位 人数"
    ,sum(amt) as "合計人数"
from tmp1 
group by 1
order by 9 desc, 1