在Oracle中,如何将多行连接成一行而不创建存储过程?[副本]

从Oracle 11gR2中 LISTAGG 第条应该起到以下作用:

SELECT question_id,
       LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE
GROUP BY question_id;

ON OVERFLOW TRUNCATE/ERROR 来处理这个问题。

容易的:

SELECT question_id, wm_concat(element_id) as elements
FROM   questions
GROUP BY question_id;

在10g上单独测试;-)

从…起 http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

Try this for a way that does not require a function. 需要注意的是,没有函数就没有简单的方法。

这是没有自定义函数的最短路径:(它使用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;

此OTN线程包含多种进行字符串聚合的方法,包括性能比较: http://forums.oracle.com/forums/message.jspa?messageID=1819487#1819487