0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Nageswara Rao J
  • 1
  • 1
  • 1
    Please fix your formatting and also tell us which _version_ of SQL you are using (e.g. MySQL, SQL Server, Oracle, etc.) – Tim Biegeleisen Apr 21 '21 at 09:50
  • https://stackoverflow.com/questions/12558509/concatenate-and-group-multiple-rows-in-oracle –  Apr 21 '21 at 10:14

1 Answers1

0

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

Gosfly
  • 1,240
  • 1
  • 8
  • 14