Get Comma Separated Ids in variable plsql

Question

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?

Have you tried a query yet? The best way to master SQL (or any language) is to practice using it. — Tim Biegeleisen, Oct 16 '18 at 07:26
currenty i am taking one by one id value and using for loop, but now i want to get all ids separated by comma and then want to use one select statement with IN clause, don't have any idea how to do this..:( — learner1, Oct 16 '18 at 07:28
tried using listagg(), it is giving result of string concatenation is too long. — learner1, Oct 16 '18 at 07:37
Does a string variable with comma separated values work inside an IN? Maybe verify that first! — Hans Keﬆing, Oct 16 '18 at 08:01
*" 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"* Why? Why do you want to that rather having an IN clause which is just `in ( select id from tbl where name = 'A')`? — APC, Oct 16 '18 at 08:17

score 2 · Accepted Answer · answered Oct 16 '18 at 07:43

2

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'

answered Oct 16 '18 at 07:43

Mick Mnemonic

7,808
2
26
30

score 0 · Answer 2 · answered Oct 16 '18 at 08:13

0

this is working:

create table ns_1111(col1 number,col2 varchar(20));

insert into ns_1111 values(1,'A');
insert into ns_1111 values(2,'A');
insert into ns_1111 values(3,'A');
insert into ns_1111 values(4,'B');
insert into ns_1111 values(5,'B');
insert into ns_1111 values(6,'C');
SELECT * FROM ns_1111;


select * from (SELECT LISTAGG(col1, ', ') WITHIN GROUP (ORDER BY col1)
FROM ns_1111 group by col2) where rownum<=1   ;

output:

1, 2, 3

answered Oct 16 '18 at 08:13

Nikhil S

3,786
4
18
32

SQL Queries are ordered non-deterministicly (unless an `ORDER BY` clause is specified). Shuffle your `INSERT` statements (or enable row-movement on your table), or try to get the results for `B`, and your query will not give the expected results as you are relying on the non-deterministic order to give you deterministic results. – MT0 Oct 16 '18 at 09:52
i ll then use order by in col1 – Nikhil S Oct 16 '18 at 09:59
@MT0 i shffled the inserts then also its showing corrrect outputs you a carelessly downgrading my answer – Nikhil S Oct 16 '18 at 10:04
[SQLFiddle](http://sqlfiddle.com/#!4/6d62a/1) gives an example of it not giving the expected results. If you use `ORDER BY col1` in the inner query then you will get `ORA-00979: not a GROUP BY expression`. – MT0 Oct 16 '18 at 10:06
my sql developer gives the correct answers is there any differenc ebetween fiddle and sql developer – Nikhil S Oct 16 '18 at 10:07
@MT0 why have you given 1 1 instead of B there – Nikhil S Oct 16 '18 at 10:08
check for this http://sqlfiddle.com/#!4/56e34/1 – Nikhil S Oct 16 '18 at 10:10
@MT0 you have inserted wrong inputs for B – Nikhil S Oct 16 '18 at 10:11
To show you where the query goes wrong - you are not filtering on `A` you are relying on an undocumented implicit alpha-numeric ordering which one of `LISTAGG` or `GROUP BY` applies when grouping the rows and then filtering to get the row with the first alpha-numeric value. This is not what the OP wanted (although it co-incidentally gets the correct value for their data) but if you change the data your query does not behave as the OP expects. – MT0 Oct 16 '18 at 10:14
@MT0 yes you are right i filtered it on first row the op wanted 1,2,3 in the IN cliause – Nikhil S Oct 16 '18 at 10:18

Get Comma Separated Ids in variable plsql

2 Answers2