with tbl as (
    select
        p.pf_code,
        p.pf_name,
        f.nation_code,
        n.nation_name,
        amt,
        rank() over (partition by p.pf_code order by amt desc, f.nation_code asc) as 'rank'
    from foreigner f
    join prefecture p on f.pf_code = p.pf_code
    join nationality n on f.nation_code = n.nation_code
    where f.nation_code <> 113
)
select
    pf_code as '都道府県コード',
    pf_name as '都道府県名',
    max(case when rank = 1 then nation_name end) as '1位 国名',
    max(case when rank = 1 then amt end) as '1位 人数',
    max(case when rank = 2 then nation_name end) as '2位 国名',
    max(case when rank = 2 then amt end) as '2位 人数',
    max(case when rank = 3 then nation_name end) as '3位 国名',
    max(case when rank = 3 then amt end) as '3位 人数',
    sum(amt) as '合計人数'
from tbl
group by 1
order by 9 desc