sql query for grouping two columns and comma separate value in 3rd column

Question

I have 3 tables Table A

col1	col2
A	1
B	2
C	3

Table B

col1	col2
1	x
2	y
3	z

Table C

col1	col2
x	test1
x	test2
x	test3
y	test4
y	test5
z	test6
z	test7

So my requirement is I need to get the result as

col1	col2
A	test1,test2,test3
B	test4,test5
C	test6, test7

can some one help, there is relation between the tables. I am using Oracle 19c

Thank you.

Answer 1

Here is what you are looking for :

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

SEE DEMO HERE