Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table A (col1 varchar(5), col2 varchar(5));
create table B (col1 varchar(5), col2 varchar(5));
create table C (col1 varchar(5), col2 varchar(5));
insert into A values('A','1');
1 rows affected

insert into A values('B','2');
1 rows affected

insert into A values('C','3');
1 rows affected

insert into B values('1','x');
1 rows affected

insert into B values('2','y');
1 rows affected

insert into B values('3','z');
1 rows affected

INSERT ALL
INTO C (col1, col2) VALUES ('x', 'test1')
INTO C (col1, col2) VALUES ('x', 'test2')
INTO C (col1, col2) VALUES ('x', 'test3')
INTO C (col1, col2) VALUES ('y', 'test4')
INTO C (col1, col2) VALUES ('y', 'test5')
INTO C (col1, col2) VALUES ('z', 'test6')
INTO C (col1, col2) VALUES ('z', 'test7')
SELECT 1 FROM DUAL;
7 rows affected

SELECT 
    A.col1,
    LISTAGG(C.col2, ',') WITHIN GROUP (ORDER BY C.col2) "col2"
FROM A
JOIN B on A.col2 = B.col1
JOIN C on B.col2 = C.col1
GROUP BY A.col1


COL1
col2


A
test1,test2,test3

B
test4,test5

C
test6,test7