select
    都道府県コード,
    都道府県名,
    max(case when r=1 then kok else null end) as "1位 国名",
    max(case when r=1 then nin else null end) as "1位 人数",
    max(case when r=2 then kok else null end) as "2位 国名",
    max(case when r=2 then nin else null end) as "2位 人数",
    max(case when r=3 then kok else null end) as "3位 国名",
    max(case when r=3 then nin else null end) as "3位 人数",
    sum(nin) as "合計人数"
from (
select
    p.PF_CODE as 都道府県コード,
    p.pf_name as 都道府県名,
    row_number() over (partition by f.pf_code order by f.amt desc, n.nation_code asc) as r,
    n.nation_name as kok,
    f.amt as nin
    -- sum(amt) as 合計人数
from
    FOREIGNER as f
    inner join NATIONALITY as n on n.NATION_CODE=f.NATION_CODE
    inner join PREFECTURE as p on p.PF_CODE=f.PF_CODE
where
    f.NATION_CODE!=113
) as z
group by z.都道府県コード
order by 合計人数 desc, 都道府県コード asc
;