Comma-Separated List as a Result of Select Statement in Oracle

comma-separated list as a result of select statement in Oracle

SELECT parent_id,
       RTRIM(XMLAGG(XMLELEMENT(e,child_id || ',')).EXTRACT('//text()'),',') AS "Children"
  FROM parentChildTable
 WHERE parent_id = 0
 GROUP BY parent_id

or

SELECT parent_id,
       LISTAGG(child_id, ',') WITHIN GROUP (ORDER BY child_id) AS "Children"
  FROM parentChildTable
 WHERE parent_id = 0
 GROUP BY parent_id

How can I combine multiple rows into a comma-delimited list in Oracle?

Here is a simple way without stragg or creating a function.

create table countries ( country_name varchar2 (100));

insert into countries values ('Albania');

insert into countries values ('Andorra');

insert into countries values ('Antigua');

SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua                                                         

1 row selected.

As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.

select listagg(country_name,', ') within group(order by country_name) csv
  from countries;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.

Oracle Query for extracting comma separated values

The problem is in the exit condition of the recursive query, that stops as soon as an empty item is met. You could change that to check the iteration index against the actual number of commas in the string:

SELECT regexp_substr(i_child_sal_acc_det, '[^,]*', 1, LEVEL)
BULK COLLECT INTO v_sal_acc_det_list
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(i_child_sal_acc_det, ',') + 1

Note that I changed the quantifier of regular expression from + to * so it allows empty strings.

If you want to limit the number of iterations to 9, even if there are more items in the CSV string:

CONNECT BY LEVEL <= LEAST(REGEXP_COUNT(i_child_sal_acc_det, ',') + 1, 9)

PL/SQL Search cell data separated by comma with IN condition

Rather than using IN, you can use:

SELECT *
FROM   table_name
WHERE  ','||column2||',' LIKE '%,AAA,%'
OR     ','||column2||',' LIKE '%,BBB,%'

Or, a table collection expression:

SELECT *
FROM   table_name t
WHERE  EXISTS (
         SELECT 1
         FROM   TABLE(SYS.ODCIVARCHAR2LIST('AAA','BBB'))
         WHERE  ','||t.column2||',' LIKE '%,'||COLUMN_VALUE||',%'
       );

Which, for the sample data:

CREATE TABLE table_name ( column1, column2 ) AS
SELECT 'data1', 'AAA,CCC,BBB' FROM DUAL UNION ALL
SELECT 'data2', 'AAA,CCC'     FROM DUAL UNION ALL
SELECT 'data3', 'BBB,DDD,EEE' FROM DUAL UNION ALL
SELECT 'data4', 'EEE,FFF,GGG' FROM DUAL;

Both output:

COLUMN1 | COLUMN2    
:------ | :----------
data1   | AAA,CCC,BBB
data2   | AAA,CCC    
data3   | BBB,DDD,EEE

db<>fiddle here

Oracle SQL comma separated parameter in where clause

You can use regexp_substr with connect by for it.

SELECT * FROM students_tbl stud WHERE stud.stud_id IN 
(Select regexp_substr(:s_id,'[^,]+', 1,level)
From dual
Connect by regexp_substr(:s_id,'[^,]+', 1, level) is not null);

Cheers!!

---

Related Topics