SQL Server 2017

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

create table t1 (id int, name varchar(50));
create table t2 (name varchar(50), "type" varchar(50));
insert into t1 values (1, 'Roy');
1 rows affected

insert into t1 values (1, 'Roy');
1 rows affected

insert into t1 values (1, 'Roy');
1 rows affected

insert into t1 values (1, 'Roy');
1 rows affected

insert into t2 values ('Roy', 'GE');
1 rows affected

insert into t2 values ('Roy', 'RA');
1 rows affected

insert into t2 values ('Roy', 'SA');
1 rows affected

insert into t2 values ('Roy', 'PA');
1 rows affected

insert into t2 values ('Roy', 'PA');
1 rows affected

select t1.Name, STRING_AGG(t2.Type, ',') as 'Type' 
from t1
left outer join t2
on t1.Name = t2.Name
Group by t1.Name


Name
Type


Roy
GE,RA,SA,PA,PA,GE,RA,SA,PA,PA,GE,RA,SA,PA,PA,GE,RA,SA,PA,PA