with tbl1 as(
    select
        row_number() over(
            partition by
                t.PF_CODE
            order by
                t.AMT desc,
                t.NATION_CODE
            ) as "順位",
        t.PF_CODE as "都道府県コード",
        m1.PF_NAME as "都道府県名",
        m2.NATION_NAME as "国名",
        t.AMT as "人数"
    from
        FOREIGNER as t
    inner join
        PREFECTURE as m1 on t.PF_CODE = m1.PF_CODE
    inner join
        NATIONALITY as m2 on t.NATION_CODE = m2.NATION_CODE
    where
        t.NATION_CODE != '113'
)
select
        "都道府県コード",
        "都道府県名",
        max(case when "順位" = 1 then "国名" else Null end) as  "1位 国名",
        max(case when "順位" = 1 then "人数" else Null end) as  "1位 人数",
        max(case when "順位" = 2 then "国名" else Null end) as  "2位 国名",
        max(case when "順位" = 2 then "人数" else Null end) as  "2位 人数",
        max(case when "順位" = 3 then "国名" else Null end) as  "3位 国名",
        max(case when "順位" = 3 then "人数" else Null end) as  "3位 人数",
        sum("人数") as "合計人数"
from
    tbl1
group by
    "都道府県コード"
order by
    "合計人数" desc,
    "都道府県コード"
;