select
    AMT_WITH_RANK.PF_CODE as '都道府県コード',
    PF_NAME as '都道府県名',
    MAX(CASE WHEN RANK=1 THEN NATION_NAME END) as '1位 国名',
    SUM(CASE WHEN RANK=1 THEN AMT END) as '1位 人数',
    MAX(CASE WHEN RANK=2 THEN NATION_NAME END) as '2位 国名',
    SUM(CASE WHEN RANK=2 THEN AMT END) as '2位 人数',
    MAX(CASE WHEN RANK=3 THEN NATION_NAME END) as '3位 国名',
    SUM(CASE WHEN RANK=3 THEN AMT END) as '3位 人数',
    SUM(AMT) as '合計人数'
from
    (
        select
            PF_CODE,
            NATION_CODE,
            AMT,
            row_number() over (
                partition by PF_CODE
                order by
                    AMT desc
            ) as rank
        from
            FOREIGNER
        where
            NATION_CODE != '113'
    )AMT_WITH_RANK
    inner join PREFECTURE on PREFECTURE.PF_CODE = AMT_WITH_RANK.PF_CODE
    inner join NATIONALITY on NATIONALITY.NATION_CODE = AMT_WITH_RANK.NATION_CODE
group by 
    AMT_WITH_RANK.PF_CODE
order by 
    SUM(AMT) desc,
    PREFECTURE.PF_CODE asc