select 
 main.PF_CODE AS 都道府県コード
,a.PF_NAME AS 都道府県名
,MAX(case when rownum = 1 then a.PF_NAME else null end)  AS  [1位 国名]
,SUM(case when rownum = 1 then sub.AMT else 0 end) AS [1位 人数]
,MAX(case when rownum = 2 then a.PF_NAME else null end)  AS  [2位 国名]
,SUM(case when rownum = 2 then sub.AMT else 0 end) AS [2位 人数]
,MAX(case when rownum = 3 then a.PF_NAME else null end)  AS  [3位 国名]
,SUM(case when rownum = 3 then sub.AMT else 0 end) AS [3位 人数]
,SUM(sub.AMT) AS [合計人数]
from (select PF_CODE from FOREIGNER group by PF_CODE) main
outer apply (select 
        row_number() over(order by AMT) as rownum
       ,NATION_CODE
       ,AMT
      from FOREIGNER
      where main.PF_CODE = PF_CODE 
      ) sub
left join PREFECTURE a
on main.PF_CODE = a.PF_CODE
group by main.PF_CODE,a.PF_NAME