select
t1.PF_CODE as 都道府県コード,
PF_NAME as 都道府県名,
case when rank_amt_by_pref = 1 then NATION_NAME else 0 end as "1位 国名", 
sum(case when rank_amt_by_pref = 1 then sum_amt else 0 end) as "1位 人数",
replace(
  group_concat(case when rank_amt_by_pref = 2 then NATION_NAME else "" end, ";")
, ";", "") as "2位 国名",
sum(case when rank_amt_by_pref = 2 then sum_amt else 0 end) as "2位 人数",
replace(
  group_concat(case when rank_amt_by_pref = 4 then NATION_NAME else "" end, ";")
, ";", "") as "3位 国名",
sum(case when rank_amt_by_pref = 3 then sum_amt else 0 end) as "3位 人数",
sum(sum_amt) as "合計人数"
from (
    select
    RANK () OVER(
        partition by PF_CODE
        order by sum_amt desc
    ) rank_amt_by_pref,
    PF_CODE,
    NATION_CODE,
    sum_amt
    from (
        select
        PF_CODE,
        NATION_CODE,
        sum(AMT) as sum_amt
        from FOREIGNER
        where NATION_CODE <> '113'
        group by PF_CODE, NATION_CODE
    ) as t0
) as t1
inner join NATIONALITY on NATIONALITY.NATION_CODE = t1.NATION_CODE
inner join PREFECTURE on PREFECTURE.PF_CODE = t1.PF_CODE
group by t1.PF_CODE
order by 合計人数 desc, 都道府県コード asc
;