4

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';

Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
brain_damage
  • 955
  • 6
  • 20
  • 29

4 Answers4

8
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
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 1
    agreed with Mark, if you are on 11gr2 ListAgg is what you want – Harrison Mar 16 '11 at 12:50
  • thank you, but I think I gave a wrong example for the question I have. My question was if a table row contains id, number(every child has its parent's number) and status (status can be child or parent), how to make a query for a table with first column - a person id, and a second column - a list of it's children id's? – brain_damage Mar 17 '11 at 07:12
  • @brain-damage - You've lost me now. The two queries I've given should both do exactly what you seem to be asking... just remove the WHERE clause which specifies a single parent (you might want to use a WHERE clause where status = 'paerent'). – Mark Baker Mar 17 '11 at 08:35
  • If this isn't what you want, then perhaps an example of your table data, and the results you want to achieve might help us – Mark Baker Mar 17 '11 at 08:36
1

Mark's implementation of LISTAGG is definitely the way to go for ORacle 11GR2. For For 11GR1 or Oracle 10 you can use wmsys.wm_Concat instead in exactly the same way (may require a permissions grant from your DBA)

Michael Broughton
  • 4,045
  • 14
  • 12
0

Just another way to approach it ...

SELECT parent_id,max(child_list) FROM (
  SELECT parent_id,sys_connect_by_path(child_number,',') child_list FROM (
    SELECT parent_id, id,
           row_number() over (partition by parent_id order by id) child_number
      FROM person
      WHERE parent_id IS NOT NULL
  )
  START WITH child_number=1
  CONNECT BY parent_id = PRIOR parent_id AND child_number = PRIOR child_number + 1
)
GROUP BY parent_id
ORDER BY parent_id
;
Dave Costa
  • 47,262
  • 8
  • 56
  • 72
-1

SELECT wmsys.wm_concat() FROM ;

It's controversial, but it works - https://forums.oracle.com/forums/thread.jspa?threadID=2205545

erichunley
  • 1