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

sql oracle subquery string-aggregation
brain_damage picture brain_damage · Mar 16, 2011 · Viewed 14.3k times · Source

I have a table named "person". It contains person's id and it's parent id (only one parent is possible). As a result of a query, I want a table with first column - a person id, and a second column - a list of it's children id's. How exactly to do this? I've read about listagg function, but I'm not sure if it is appropriate for my purpose. And this query produces an empty second column:

select t1.id, (select t2.id from person t2 where t2.parent_id = t1.id) from person t1 where t1.status = 'parent';

Answer

Mark Baker picture Mark Baker · Mar 16, 2011 
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

Related questions

Oracle: Combine multiple results in a subquery into a single comma-separated value

I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values. This is identical to this question, but for Oracle rather than SQL Server or MySQL.

Read More
subquery in FROM must have an alias

I have this query I have written in PostgreSQL that returns an error saying: [Err] ERROR: LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge This is the whole query: SELECT COUNT (made_only_recharge) AS made_only_recharge FROM ( …

Read More
Find duplicate entries in a column

I am writing this query to find duplicate CTN Records in table1. So my thinking is if the CTN_NO appears more than twice or higher , I want it shown in my SELECT * statement output on top. I tried the …

Read More