select
    "都道府県コード",
    "都道府県名",
    max(
        case
            when "順位" == 1 then "国名"
            else 0
        end
    ) as "1位 国名",
    max(
        case
            when "順位" == 1 then "人数"
            else 0
        end
    ) as "1位 人数",
    max(
        case
            when "順位" == 2 then "国名"
            else 0
        end
    ) as "2位 国名",
    max(
        case
            when "順位" == 2 then "人数"
            else 0
        end
    ) as "2位 人数",
    max(
        case
            when "順位" == 3 then "国名"
            else 0
        end
    ) as "3位 国名",
    max(
        case
            when "順位" == 3 then "人数"
            else 0
        end
    ) as "3位 人数",
    sum("人数") as "合計人数"
from
    (
        select
            P.PF_CODE as "都道府県コード",
            P.PF_NAME as "都道府県名",
            N.NATION_CODE as "国コード",
            N.NATION_NAME as "国名",
            F.AMT as "人数",
            rank() over (
                partition by P.PF_CODE
                order by
                    F.AMT desc,
                    F.NATION_CODE
            ) as "順位"
        from
            FOREIGNER as F,
            NATIONALITY as N,
            PREFECTURE as P
        where
            P.PF_CODE == F.PF_CODE
            and N.NATION_CODE == F.NATION_CODE
            and N.NATION_CODE != "113"
    )
group by
    "都道府県コード"
order by
    "合計人数" desc