select
    "都道府県コード"
    , "都道府県名"
    , max(case when "県内順位" = 1 then "国名" else null end) as "1位 国名"
    , max(case when "県内順位" = 1 then "外国人数" end) as "1位 人数"
    , max(case when "県内順位" = 2 then "国名" else null end) as "2位 国名"
    , max(case when "県内順位" = 2 then "外国人数" end) as "2位 人数"
    , max(case when "県内順位" = 3 then "国名" else null end) as "3位 国名"
    , max(case when "県内順位" = 3 then "外国人数" end) as "3位 人数"
    , sum("外国人数") as "合計人数"
from(
    select
        FOREIGNER.PF_CODE as "都道府県コード"
        , PF_NAME as "都道府県名"
        , NATION_NAME as "国名"
        , sum(AMT) as "外国人数"
        , rank() over(partition by FOREIGNER.PF_CODE order by sum(AMT) desc, FOREIGNER.NATION_CODE asc) as "県内順位"
        
    from
        FOREIGNER
    inner join
        PREFECTURE
    on
        FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
    inner join 
        NATIONALITY
    on
        FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
    where
        NATION_NAME != "その他"
    group by
        FOREIGNER.PF_CODE
        , FOREIGNER.NATION_CODE
)
group by
    "都道府県コード"
order by
    "合計人数" desc
    , "都道府県コード" asc;