SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEST(parts nvarchar(10),flag nvarchar(10),groupno nvarchar(10));
insert into TEST
values ('p1','m','01'),
('p2','r','01'),
('p3','m','02'),
('p4','r','02'),
('p5','r','02'),
('p6','m','03');
6 rows affected

select *
from TEST
where 1=1
order by parts


parts
flag
groupno


p1
m
01

p2
r
01

p3
m
02

p4
r
02

p5
r
02

p6
m
03


select *
from TEST
where 1=1
and flag='m'
order by parts


parts
flag
groupno


p1
m
01

p3
m
02

p6
m
03


select *
from TEST
where 1=1
and flag='r'
order by parts


parts
flag
groupno


p2
r
01

p4
r
02

p5
r
02


select groupno,
       partsnew=string_agg(parts,',')
from TEST
where 1=1
and flag='r'
group by groupno


groupno
partsnew


01
p2

02
p4,p5


select TEST.*,M.partsnew
from TEST
--
left join (
select groupno,
       partsnew=string_agg(parts,',')
from TEST
where 1=1
and flag='r'
group by groupno
) as M on M.groupno=TEST.groupno
--
where 1=1
and flag='m'
order by parts


parts
flag
groupno
partsnew


p1
m
01
p2

p3
m
02
p4,p5

p6
m
03
null