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