Oracle 11g Release 2

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

create table t1 (id number, name varchar2(50));
create table t2 (name varchar2(50), "type" varchar2(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 A.ID, LISTAGG(B."type", ',') WITHIN GROUP (ORDER BY B.NAME) "TYPE"
FROM 
(SELECT T1.NAME, T1.ID
FROM T1
GROUP BY T1.NAME, T1.ID) A
JOIN 
(SELECT T2.NAME, T2."type"
FROM T2
GROUP BY T2.NAME, T2."type") B
ON A.NAME = B.NAME
GROUP BY A.ID;


ID
TYPE


1
GE,PA,RA,SA