select
     都道府県コード
    ,都道府県名
    ,max(国名) FILTER (WHERE 順位 = 1) as "1位 国名"
    ,sum(人数) FILTER (WHERE 順位 = 1) as "1位 人数"
    ,max(国名) FILTER (WHERE 順位 = 2) as "2位 国名"
    ,sum(人数) FILTER (WHERE 順位 = 2) as "2位 人数"
    ,max(国名) FILTER (WHERE 順位 = 3) as "3位 国名"
    ,sum(人数) FILTER (WHERE 順位 = 3) as "3位 人数"
    ,sum(人数) as "合計人数"
from
(
select
     main.PF_CODE    as 都道府県コード
    ,pre.PF_NAME     as 都道府県名
    ,nat.NATION_NAME as 国名
    ,main.AMT        as 人数
    ,RANK() OVER(partition by main.PF_CODE ORDER BY main.AMT DESC, main.NATION_CODE asc) as 順位
from
    FOREIGNER as main
inner join
    NATIONALITY as nat using(NATION_CODE)
inner join
    PREFECTURE as pre using(PF_CODE)
where
    main.NATION_CODE <> '113'
)
group by
     都道府県コード
    ,都道府県名
order by
    合計人数 desc
    ,都道府県コード asc