select 
t1.PF_CODE as "都道府県コード",
pre.PF_NAME as "都道府県名",
group_concat(case "順位" when 1 then NATION_NAME else NULL end) as "1位 国名",
SUM(case "順位" when 1 then AMT else 0 end) as "1位 人数",
group_concat(case "順位" when 2 then NATION_NAME else NULL end)as "2位 国名",
SUM(case "順位" when 2 then AMT else 0 end) as "2位 人数",
group_concat(case "順位" when 3 then NATION_NAME else NULL end)  as "3位 国名",
SUM(case "順位" when 3 then AMT else 0 end) as "3位 人数",
SUM(AMT) as "合計人数"
from
(select f1.PF_CODE,
rank() over(partition by f1.PF_CODE order by AMT desc) as "順位",
AMT,
NATION_CODE
from FOREIGNER as f1
where NATION_CODE <> 113
) as t1
inner join PREFECTURE as pre
on t1.PF_CODE=pre.PF_CODE
inner join NATIONALITY  as n1
on n1.NATION_CODE=t1.NATION_CODE
group by t1.PF_CODE
order by "合計人数" desc,"都道府県コード";