甲骨文串联与逗号
可能重复:
How can I combine multiple rows into a comma-delimited list in Oracle?甲骨文串联与逗号
能有一个人请告诉我如何实现以下?
表:
efforts_id cycle_name release_name
123 quarter march
123 half april
123 full april
124 quarter may
我的预期输出:
efforts_id cycle_name release_name
123 quarter,half,full march,april
124 quarter may
我在甲骨文所以不知道如何做到这一点初学者。任何帮助,将不胜感激。
感谢
您将需要使用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
你需要的是“字符串聚合”。蒂姆·霍尔的优秀的网站显示您根据甲骨文的确切版本的替代你:http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php
在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;
注意使用wm_concat功能的是Oracle不受支持......
+1,并且记住LISTAGG由4000个字节的限制。 –
因为它是一个varchar,而不是clob。有传言说,在Oracle 12c中 - 大约在今年年底 - 将会在数据库中出现多达32767的变化,就像在PL/SQL中一样... –
@ErkanHaspulat 4000字符,当然?! –
如果你的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
如果不是,this article显示了替代方法。
真棒...谢谢bluefeet – Jeevan
@Jeevan欢迎您,可能可以清理这个有点使用'带'条款而不是子选择,以您的偏好为准 – Taryn
嗯好吧..我不知道如何去做。 (我是新的oracle.brand新:))。我现在会回答你的答案。 – Jeevan