All geek questions in one place

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

I have a table called "man." It contains the identity identifier and its parent identifier (only one parent is possible). As a result of the query, I need a table with the first column - the identifier of the person, and the second column - a list of his child identifiers. How to do this? I read about the listagg function, but I'm not sure if it is suitable for my purpose. And this query creates 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';

+4
source share
4 answers
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
+7
source

To mark the implementation of LISTAGG is, of course, the path to ORACLE 11GR2. For 11GR1 or Oracle 10, you can use wmsys.wm_Concat instead the exact same way (you may need to provide permission from your administrator)

+1
source

Another way to get close to him ...

 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 ;
0
source

SELECT wmsys.wm_concat () FROM;

This is inconsistent, but it works - https://forums.oracle.com/forums/thread.jspa?threadID=2205545

-1
source

Source: https://habr.com/ru/post/1343914/

More articles:


All Articles