with t as (select * from FOREIGNER
left join NATIONALITY using (NATION_CODE)
left join PREFECTURE using (PF_CODE)
where NATION_NAME != "その他"),
t1 as (select t.*, rank() over(partition by PF_CODE order by AMT desc, NATION_CODE asc) as rank
from t
)
-- select * from t1
select "都道府県コード","都道府県名","1位 国名","1位 人数","2位 国名","2位 人数","3位 国名","3位 人数","合計人数"
from (select PF_CODE as "都道府県コード", PF_NAME as "都道府県名", sum(AMT) as "合計人数" from t group by PF_CODE, PF_NAME)
left join (select PF_CODE as "都道府県コード", PF_NAME as "都道府県名", NATION_NAME as "1位 国名", AMT as "1位 人数" from t1 where rank = 1) using ("都道府県コード", "都道府県名")
left join (select PF_CODE as "都道府県コード", PF_NAME as "都道府県名", NATION_NAME as "2位 国名", AMT as "2位 人数" from t1 where rank = 2) using ("都道府県コード", "都道府県名")
left join (select PF_CODE as "都道府県コード", PF_NAME as "都道府県名", NATION_NAME as "3位 国名", AMT as "3位 人数" from t1 where rank = 3) using ("都道府県コード", "都道府県名")
order by "合計人数" desc, "都道府県コード" asc