Rishi Deorukhkar Rishi Deorukhkar - 5 months ago 12
SQL Question

How to use comma separated values from listagg in inner query?

Consider the query

select listagg(''''||Name||'''', ',') within group (order by Name) from STUDENTS;


This gives me output as

'Jon','Rob','Bran'


How do I use this in inner query consider the following example:

with lst as(
select listagg(''''||Name||'''', ',') within group (order by Name) as name_list from STUDENTS)
select * from result where Name in (select name_list from lst)


Expected result :

-----------------
| Name | Score |
-----------------
| Jon | 80 |
-----------------
| Rob | 60 |
-----------------
| Bran | 75 |
-----------------


But the actual result does not return any rows as it is considering the sub query as a single field.

How can this be handled ?

Answer

The output of a listagg analytical function is text data. Thus, even if you think you are getting

'Jon','Rob','Bran'

as the output, it is actually a single string like

'''Jon'', ''Rob'', ''Bran'''

For your purpose the answer by @artm should suffice. Otherwise, if you're bound to do what you're trying to do then you will need to use dynamic SQL like this:

declare
    p_cur sys_refcursor;
    name_list clob;
    select_sql clob;
begin
    select listagg(''''||Name||'''', ',')
               within group (order by Name) as name_list
      into name_list
      from STUDENTS;

    select_sql := 'select * from result where name in (' || name_list || ')';

    open p_cur for select_sql;
end;
/
Comments