SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

9 rows affected

with norm as (
  select id, Name1, 'current' as sem, current_sem as subj from test1
  union
  select id, Name1, 'next' as sem, next_sem as subj from test1
  union
  select id, Name1, 'prev' as sem, prev_sem as subj from test1
), rules as (
  select id, Name1, 'add' as change, subj
    from norm t
   where sem = 'next'
     and not exists (
           select 1
             from norm
            where id = t.id 
              and sem != t.sem
              and subj = t.subj
       )
  union all
  select id, Name1, 'remove' as change, subj
    from norm t
   where sem = 'current'
     and not exists (
           select 1
             from norm
            where id = t.id
              and sem != t.sem
              and subj = t.subj
       )
)
select t.id, t.Name1, 
       string_agg(
         case
           when r.change = 'add' then subj
           else null
         end,
         ';'
       ) as sub_to_add,
       string_agg(
         case 
           when r.change = 'remove' then subj
           else null
         end,
         ';'
       ) as sub_to_remove
  from (select distinct id, Name1 from test1) t
       left join rules r 
         on r.id = t.id
 group by t.id, t.Name1
 order by t.id
 ;

id	Name1	sub_to_add	sub_to_remove
R001	Michael	Maths	null
R002	John	null	null
R003	Tim	Chemistry;Civics	Drama;History