Search code examples
sqloraclestring-aggregation

Get Comma Separated Ids in variable plsql

I have a table like this

id  |  Name
===========
1   |  A
2   |  A
3   |  A
4   |  B
5   |  B
6   |  C

i am writing select id from tbl where name = "A", i want to get all three ids (1,2,3) like this separated by comma in a single variable and then I want to use that variable in another select query having IN clause, any help please?

Solution

  • As others have pointed out, using listagg() should do the trick:

    SELECT listagg(id, ',') WITHIN GROUP (ORDER BY id) as concatenation 
FROM mytable 
WHERE name = 'A'