select
    tmp.pf_code 都道府県コード, 
    tmp.pf_name 都道府県名,
    max(case when tmp.rank = 1 then tmp.nation_name else null end) '1位 国名',
    sum(case when tmp.rank = 1 then tmp.amt else 0 end) '1位 人数',
    max(case when tmp.rank = 2 then tmp.nation_name else null end) '2位 国名',
    sum(case when tmp.rank = 2 then tmp.amt else 0 end) '2位 人数',
    max(case when tmp.rank = 3 then tmp.nation_name else null end) '3位 国名',
    sum(case when tmp.rank = 3 then tmp.amt else 0 end) '3位 人数',
    sum(tmp.amt) 合計人数
from
(
    select
        p.pf_code,
        p.pf_name,
        f.amt,
        n.nation_name,
        row_number() over(partition by p.pf_code order by f.amt desc) 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 1 = 1
    and f.nation_code != '113'
)tmp
group by tmp.pf_code, tmp.pf_name
order by 合計人数 desc, 都道府県コード asc
;