with tmp as(
select
a.PF_CODE as 都道府県コード,
b.PF_NAME as 都道府県名,
c.NATION_NAME as 国名,
a.amt,
Row_Number() over(partition by a.PF_CODE
                  order by a.amt desc,a.NATION_CODE) as rn
  from FOREIGNER a
  join PREFECTURE b
    on b.PF_CODE = a.PF_CODE
  join NATIONALITY c
    on c.NATION_CODE = a.NATION_CODE
 where c.NATION_CODE != '113')
select 都道府県コード,都道府県名,
max(case rn when 1 then 国名 end) as "1位 国名",
max(case rn when 1 then amt end)  as "1位 人数",
max(case rn when 2 then 国名 end) as "2位 国名",
max(case rn when 2 then amt end)  as "2位 人数",
max(case rn when 3 then 国名 end) as "3位 国名",
max(case rn when 3 then amt end)  as "3位 人数",
sum(amt) as 合計人数
  from tmp
group by 都道府県コード,都道府県名
order by 合計人数 desc,都道府県コード