select a.PF_CODE as 都道府県コード
      ,max(b.PF_NAME) as 都道府県名
      ,max(case when a.rank = 1 then a.NATION_NAME else null end) AS [1位 国名]
      ,sum(case when a.rank = 1 then a.AMT else null end) AS [1位 人数]
      ,max(case when a.rank = 2 then a.NATION_NAME else null end) AS [2位 国名]
      ,sum(case when a.rank = 2 then a.AMT else null end) AS [2位 人数]
      ,max(case when a.rank = 3 then a.NATION_NAME else null end) AS [3位 国名]
      ,sum(case when a.rank = 3 then a.AMT else null end) AS [3位 人数]
      ,sum(a.AMT) as 合計人数
from (
    select ROW_NUMBER() OVER(PARTITION BY a.PF_CODE ORDER BY a.AMT DESC,a.NATION_CODE) as rank
          ,a.PF_CODE
          ,a.AMT
          ,c.NATION_NAME
    from FOREIGNER a
    natural join NATIONALITY c
    where a.NATION_CODE <> '113'
)a
natural join PREFECTURE b

group by a.PF_CODE
order by sum(a.AMT) DESC,a.PF_CODE asc