select
    S.code as 都道府県コード,
    S.pf_name as 都道府県名,
    group_concat(case when s.rank_country = 1 then S.county_name else '' end,'' )as '1位 国名',
    sum(case when s.rank_country = 1 then S.AMT else 0 end )as '1位 人数',
    group_concat(case when s.rank_country = 2 then S.county_name else '' end ,'' )as '2位 国名',
    sum(case when s.rank_country = 2 then S.AMT else 0 end )as '2位 人数',
    group_concat(case when s.rank_country = 3 then S.county_name else '' end  ,'')as '3位 国名',
    sum(case when s.rank_country = 3 then S.AMT else 0 end )as '3位 人数',
    sum(S.AMT) as 合計人数
from(
    select
        FO.PF_CODE as code,
        PR.PF_NAME as pf_name,
        NA.NATION_NAME as county_name,
        ROW_NUMBER() over (partition by FO.PF_CODE order by FO.AMT desc,FO.NATION_CODE asc) as rank_country,
        FO.AMT as AMT
    from
        FOREIGNER as FO
        inner join PREFECTURE as PR on PR.PF_CODE = FO.PF_CODE
        inner join NATIONALITY as NA on NA.NATION_CODE = FO.NATION_CODE
    where
        FO.NATION_CODE <> '113'
    ) as S

group by 都道府県コード
order by 
合計人数 desc,
都道府県コード asc;