Вывод специальностей человека в разных столбцах

SELECT 
  ed4.Person, MIN(Education1) Education1, MIN(Education2) Education2, MIN(Education3) Education3,
    MIN(Education4) Education4, MIN(ed5.Id) Education5  
FROM (
  SELECT 
    ed3.Person, MIN(Education1) Education1, MIN(Education2) Education2, MIN(Education3) Education3,
    MIN(ed4.Id) Education4  
  FROM (
    SELECT ed2.Person, MIN(Education1) Education1, MIN(Education2) Education2, MIN(ed3.Id) Education3  
    FROM (
      SELECT ed1.Person, MIN(Education1) Education1, MIN(ed2.Id) Education2  
      FROM
        (SELECT Person, MIN(Id) Education1 FROM VPRVT_Educations WHERE SpecialityName IS NOT NULL GROUP BY Person) ed1
        LEFT JOIN VPRVT_Educations ed2 ON ed1.Person = ed2.Person AND ed2.Id > Education1 and SpecialityName IS NOT NULL
      GROUP BY ed1.Person
    ) ed2
      LEFT JOIN VPRVT_Educations ed3 ON ed2.Person = ed3.Person AND ed3.Id > Education2 and SpecialityName IS NOT NULL
  GROUP BY ed2.Person
  ) ed3
    LEFT JOIN VPRVT_Educations ed4 ON ed3.Person = ed4.Person AND ed4.Id > Education3 and SpecialityName IS NOT NULL
  GROUP BY ed3.Person
) ed4
  LEFT JOIN VPRVT_Educations ed5 ON ed4.Person = ed5.Person AND ed5.Id > Education4 and SpecialityName IS NOT NULL
GROUP BY ed4.Person

/*Preparing test table and data*/
declare @t table (id int, speciality_id int, unique (id, speciality_id));
 
insert into @t
select
 a.id, b.speciality_id
from
 (select top (10) row_number() over (order by (select 1)) from master.dbo.spt_values) a(id) cross apply
 (select top (cast(rand(checksum(newid())) * 5 as int) + 1) (cast(rand(checksum(newid())) * 100 as int) + 1) * 10 + row_number() over (order by (select 1)) from master.dbo.spt_values) b(speciality_id);
 
/*Old school*/
select
 id,
 min(case when rn = 1 then speciality_id end) as speciality1,
 min(case when rn = 2 then speciality_id end) as speciality2,
 min(case when rn = 3 then speciality_id end) as speciality3,
 min(case when rn = 4 then speciality_id end) as speciality4,
 min(case when rn = 5 then speciality_id end) as speciality5
from
 (
  select
   a.id, a.speciality_id, count(*) as rn
  from
   @t a join
   @t b on b.id = a.id and b.speciality_id <= a.speciality_id
  group by
   a.id, a.speciality_id
 ) c
group by
 id;
 
/*Modern school*/
with a as
(
 select
  id, speciality_id,
  row_number() over (partition by id order by (select 1)) as rn
 from
  @t
)
select
 id,
 min(case when rn = 1 then speciality_id end) as speciality1,
 min(case when rn = 2 then speciality_id end) as speciality2,
 min(case when rn = 3 then speciality_id end) as speciality3,
 min(case when rn = 4 then speciality_id end) as speciality4,
 min(case when rn = 5 then speciality_id end) as speciality5
from
 a
group by
 id;
 
/*Exotic*/
select
 a.id,
 c.n.value('speciality_id[1]', 'int') as speciality1,
 c.n.value('speciality_id[2]', 'int') as speciality2,
 c.n.value('speciality_id[3]', 'int') as speciality3,
 c.n.value('speciality_id[4]', 'int') as speciality4,
 c.n.value('speciality_id[5]', 'int') as speciality5
from
 (select distinct id from @t) a cross apply
 (select speciality_id from @t where id = a.id for xml path(''), root('speciality'), type) b(x) cross apply
 b.x.nodes('speciality') c(n);