with ranking as (
    select F.pf_code
    , (select P.pf_name from prefecture as P where P.pf_code = F.pf_code) as pf_name
    , F.nation_code
    , ( select N.nation_name from nationality as N where N.nation_code = F.nation_code) as nation_name
    , F.amt
    , rank() over ( partition by pf_code order by amt desc , nation_code ) as rank
    from foreigner as F
    where F.nation_code != '113'
)
select pf_code as '都道府県コード'
    , max (pf_name) as '都道府県名'
    , max ( case rank when 1 then nation_name else NULL end ) as '1位 国名'
    , max ( case rank when 1 then amt else NULL end )as '1位 人数'
    , max ( case rank when 2 then nation_name else NULL end ) as '2位 国名'
    , max ( case rank when 2 then amt else NULL end )as '2位 人数'
    , max ( case rank when 3 then nation_name else NULL end ) as '3位 国名'
    , max ( case rank when 3 then amt else NULL end )as '3位 人数'  
    ,  sum(amt) as '合計人数'
from ranking 
group by pf_code
order by sum(amt) desc , pf_code