with rank as
  (
   SELECT
     PF_CODE
     , NATION_CODE
     , AMT
     , row_number() OVER(partition by pf_code order by amt DESC,nation_code ASC) AS rank
   from
     foreigner
   where
     nation_code <> 113 --その他除く
  )
, rank2 as
(
select
  pf_code
  , group_concat(case when rank = 1 then nation_code else null END) AS no1_nation_code
  , SUM(case when rank = 1 then amt         else 0 END) AS no1_nation_amt
  , group_concat(case when rank = 2 then nation_code else null END) AS no2_nation_code
  , SUM(case when rank = 2 then amt         else 0 END) AS no2_nation_amt
  , group_concat(case when rank = 3 then nation_code else null END) AS no3_nation_code
  , SUM(case when rank = 3 then amt         else 0 END) AS no3_nation_amt
  , sum(amt) AS sum_amt
from
  rank
GROUP BY
  1
)

select
  rank2.pf_code           as '都道府県コード'
  , pf_name         as '都道府県名'
  , no1.nation_name as '1位 国名'
  , no1_nation_amt  as '1位 人数'
  , no2.nation_name as '2位 国名'
  , no2_nation_amt  as '2位 人数'
  , no3.nation_name as '3位 国名'
  , no3_nation_amt  as '3位 人数'
  , sum_amt         as '合計人数'
from
  rank2
INNER JOIN
  nationality AS no1
ON
  no1.nation_code = rank2.no1_nation_code
INNER JOIN
  nationality as no2
ON
  no2.nation_code = rank2.no2_nation_code
INNER JOIN
  nationality as no3
ON
  no3.nation_code = rank2.no3_nation_code
inner join
  prefecture
on 
  rank2.pf_code = prefecture.pf_code
order by
  9 DESC, 1 asc