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
  , STRING_AGG(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
  , STRING_AGG(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
  , STRING_AGG(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
from
  rank
GROUP BY
  1
)

select
  pf_code
  , pf_name
  , no1_nation_name
  , no1_nation_amt
  , no2_nation_name
  , no2_nation_amt
  , no3_nation_name
  , no3_nation_amt
from
  rank2
INNER JOIN
  nationality
ON
  nationality.nation_code = rank2.no1_nation_code
INNER JOIN
  nationality
ON
  nationality.nation_code = rank2.no2_nation_code
INNER JOIN
  nationality
ON
  nationality.nation_code = rank2.no3_nation_code
inner join
  prefecture
on rank2.pf_code = prefecture.pf_code