作为 Oracle 中 select 语句的结果的逗号分隔列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5324996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
comma-separated list as a result of select statement in Oracle
提问by brain_damage
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:
我有一个名为“人”的表。它包含人的 ID 和它的父 ID（只有一个父是可能的）。作为查询的结果，我想要一个表，其中第一列是个人 ID，第二列是它的孩子 ID 的列表。如何做到这一点？我已经阅读了有关 listagg 函数的内容，但我不确定它是否适合我的目的。这个查询产生一个空的第二列：
select t1.id, (select t2.id from person t2 where t2.parent_id = t1.id) from person t1 where t1.status = 'parent';
选择 t1.id，（从人 t2 中选择 t2.id，其中 t2.parent_id = t1.id）从人 t1 中，其中 t1.status = 'parent';
回答by Mark Baker
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
回答by Michael Broughton
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)
Mark 对 LISTAGG 的实现绝对是适用于 ORacle 11GR2 的方法。对于 11GR1 或 Oracle 10，您可以以完全相同的方式使用 wmsys.wm_Concat（可能需要您的 DBA 授予权限）
回答by Dave Costa
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
;
回答by erichunley
SELECT wmsys.wm_concat() FROM ;
选择 wmsys.wm_concat() FROM ;
It's controversial, but it works - https://forums.oracle.com/forums/thread.jspa?threadID=2205545
这是有争议的，但它有效 - https://forums.oracle.com/forums/thread.jspa?threadID=2205545
