如何在不创建存储过程的情况下将多行连接成Oracle中的一行?
问题描述:
如何在不创建存储过程的情况下在oracle中实现以下功能?如何在不创建存储过程的情况下将多行连接成Oracle中的一行?
数据集:
question_id element_id
1 7
1 8
2 9
3 10
3 11
3 12
所需的结果:
question_id element_id
1 7,8
2 9
3 10,11,12
答
有很多方式做字符串聚集,但最简单的是一个用户定义的函数。作为一个说明,没有这个功能没有简单的方法。
这是一个没有一个自定义函数的最短路线:(它使用ROW_NUMBER()和SYS_CONNECT_BY_PATH函数)
SELECT questionid,
LTRIM(MAX(SYS_CONNECT_BY_PATH(elementid,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS elements
FROM (SELECT questionid,
elementid,
ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) AS curr,
ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) -1 AS prev
FROM emp)
GROUP BY questionid
CONNECT BY prev = PRIOR curr AND questionid = PRIOR questionid
START WITH curr = 1;
答
容易:
SELECT question_id, wm_concat(element_id) as elements
FROM questions
GROUP BY question_id;
Pesonally上10克;-)
测试从http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
+3
不过,请注意wm_concat是一个未公开的函数。所以你可能要在生产代码中使用它之前考虑三次。 – 2009-07-02 19:34:56
答
此OTN线程包含s everal方式做字符串聚集,包括性能对比:http://forums.oracle.com/forums/message.jspa?messageID=1819487#1819487
答
对于Oracle 11gR2中,该LISTAGG条款应做的伎俩:
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE
GROUP BY question_id;
伟大的作品! ROW_NUMBER()是我缺少使sys_connect_by_path为我工作的原因。 – 2009-07-06 12:22:53
注意,当施加来连接某个域的值可能包含分离这种技术,下面的错误被抛出:`ORA-30004:使用SYS_CONNECT_BY_PATH功能时,不能有隔离板,柱value.`的一部分...如果合并的值恰好超过4000个字节的最大长度,你会得到错误:`ORA-01489:字符串连接的结果是太long.` – Somu 2013-06-04 07:20:26