declare @t table (col1 int, col2 int, col3 varchar(10), col4 varchar(10));

insert into @t
values
 (1, 1, 'a', 'b'),
 (1, 1, 'a', 'b'),
 (1, 1, 'c', 'd');

with t as
(
 select
  col1, col2,
  string_agg(col3, ',') as col3__agg,
  string_agg(col4, ',') as col4__agg
 from
  @t
 group by
  col1, col2
)
select
 t.col1, t.col2, a.clo3_agg, b.clo4_agg
from
 t outer apply
 (select string_agg(x.value, ',') from (select distinct value from string_split(t.col3__agg, ',')) x) a(clo3_agg) outer apply
 (select string_agg(x.value, ',') from (select distinct value from string_split(t.col4__agg, ',')) x) b(clo4_agg);