Oracle 用逗号连接列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13142481/
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
Oracle concatenation of columns with comma
提问by Jap Evans
Possible Duplicate:
How can I combine multiple rows into a comma-delimited list in Oracle?
Could some one please tell me how to achieve the following?
有人可以告诉我如何实现以下目标吗？
Table:
桌子：
efforts_id cycle_name release_name
123 quarter march
123 half april
123 full april
124 quarter may
My expected output:
我的预期输出：
efforts_id cycle_name release_name
123 quarter,half,full march,april
124 quarter may
I am a beginner in oracle so not sure how to do this. Any help would be appreciated.
我是 oracle 的初学者，所以不知道如何做到这一点。任何帮助，将不胜感激。
Thanks
谢谢
回答by Colin 't Hart
What you need is "string aggregation". Tim Hall's excellent site shows the alternatives you have depending on the exact version of Oracle you have: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php
您需要的是“字符串聚合”。Tim Hall 的优秀站点显示了您拥有的替代方案，具体取决于您拥有的 Oracle 的确切版本：http: //www.oracle-base.com/articles/misc/string-aggregation-techniques.php
In 11gR2 (current at time of writing), you should use the listagg function:
在 11gR2（撰写本文时的当前版本）中，您应该使用 listagg 函数：
select
efforts_id,
listagg(cycle_name, ',') within group (order by cycle_name) as cycle_name,
listagg(release_name, ',') within group (order by release_name) as release_name
from my_table
group by efforts_id;
Note that the use of the wm_concat function is unsupported by Oracle...
请注意，Oracle 不支持 wm_concat 函数的使用...
回答by Taryn
You will want to use LISTAGG()to perform this task. The other answers don't remove any of the duplicate values, to remove the duplicates, you can use something similar to this:
您将要使用它LISTAGG()来执行此任务。其他答案不会删除任何重复值，要删除重复值，您可以使用类似于以下内容的内容：
select c.efforts_id,
c.cycle_name,
listagg(r.release_name, ', ') within group (order by c.efforts_id) as release_name
from
(
select efforts_id,
listagg(cycle_name, ', ') within group (order by efforts_id) as cycle_name
from yourtable
group by efforts_id
) c
inner join
(
select distinct efforts_id, release_name
from yourtable
) r
on c.efforts_id = r.efforts_id
group by c.efforts_id, c.cycle_name
回答by Codo
If you have Oracle 11g R2, then LISTAGG is the preferred way to do it:
如果您有 Oracle 11g R2，那么 LISTAGG 是执行此操作的首选方法：
SELECT efforts_id,
LISTAGG(cycle_name) WITHIN GROUP(ORDER BY cycle_name),
LISTAGG(release_name) WITHIN GROUP(ORDER BY cycle_name)
FROM MY_TABLE
GROUP BY efforts_id
If not, this articleshows the alternative ways of doing it.
如果没有，本文将展示替代方法。
回答by ppeterka
Through the WM_concatfunction (and GROUP BYof course)
通过WM_concat函数（GROUP BY当然还有）
SELECT efforts_id, wm_concat(cycle_name), wm_concat(release_name)
FROM MY_TABLE
GROUP BY efforts_id
Hmmm, just found this:
嗯，刚发现这个：
Note that WM_CONCATis undocumented and unsupportedby Oracle, meaning it should not be used in production systems The LISTAGGfunction, which can produce the same output as WM_CONCATis both documented and supported by Oracle.
注意，WM_CONCAT是未记录的和不支持由Oracle，这意味着它不应该在生产系统中使用的LISTAGG功能，可产生相同的输出作为WM_CONCAT既记录和Oracle支持。
