SELECT
t1.PF_CODE as 都道府県コード,
t3.PF_NAME as 都道府県名,
max(case when t1.amt_rank = 1 then t2.NATION_NAME else 0 end)     as "1位 国名" ,
max(case when t1.amt_rank = 1 then t1.amt else 0 end)            as "1位 人数",
max(case when t1.amt_rank = 2 then t2.NATION_NAME else 0 end)    as "2位 国名" ,
max(case when t1.amt_rank = 2 then t1.amt else 0 end )           as"2位 人数",
max(case when t1.amt_rank = 3 then t2.NATION_NAME else 0 end)    as "3位 国名" ,
max(case when t1.amt_rank = 3 then t1.amt else 0 end )           as "3位 人数",
sum(amt) as 合計人数

from(
    SELECT
    pf_code,
    nation_code,
    amt,
    ROW_NUMBER() over(partition by pf_code order by amt desc ) as amt_rank
    from FOREIGNER
    where nation_code <> 113 
    )as t1

left outer join
NATIONALITY as t2 
on t1.nation_code = t2.nation_code

left outer join
PREFECTURE as t3
on t1.pf_code = t3.pf_code

group by 都道府県コード, 都道府県名

order by
合計人数 desc,
 都道府県コード asc