SQL 如何在不创建存储过程的情况下在 Oracle 中将多行连接成一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1076011/
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
How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
提问by Dan Polites
How can I achieve the following in oracle without creating a stored procedure?
如何在不创建存储过程的情况下在 oracle 中实现以下功能？
Data Set:
数据集：
question_id element_id
1 7
1 8
2 9
3 10
3 11
3 12
Desired Result:
预期结果：
question_id element_id
1 7,8
2 9
3 10,11,12
采纳答案by jle
There are many way to do the string aggregation, but the easiest is a user defined function. Try this for a way that does not require a function.As a note, there is no simple way without the function.
有很多方法可以进行字符串聚合，但最简单的是用户定义的函数。 试试这个不需要函数的方式。请注意，没有该功能就没有简单的方法。
This is the shortest route without a custom function: (it uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions )
这是没有自定义函数的最短路径：（它使用 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;
回答by Emmanuel
From Oracle 11gR2, the LISTAGGclause should do the trick:
从 Oracle 11gR2 开始，LISTAGG子句应该可以解决问题：
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE
GROUP BY question_id;
Beware if the resulting string is too big (more than 4000 chars for a VARCHAR2, for instance): from version 12cR2, we can use ON OVERFLOW TRUNCATE/ERRORto deal with this issue.
请注意结果字符串是否太大（例如，VARCHAR2 超过 4000 个字符）：从 12cR2 版本开始，我们可以使用ON OVERFLOW TRUNCATE/ERROR来处理这个问题。
回答by hegemon
Easy:
简单：
SELECT question_id, wm_concat(element_id) as elements
FROM questions
GROUP BY question_id;
Pesonally tested on 10g ;-)
在 10g 上进行了个人测试 ;-)
From http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
来自http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
回答by Rob van Wijk
This OTN-thread contains several ways to do string aggregation, including a performance comparison: http://forums.oracle.com/forums/message.jspa?messageID=1819487#1819487
这个 OTN 线程包含几种进行字符串聚合的方法，包括性能比较：http: //forums.oracle.com/forums/message.jspa?messageID=1819487#1819487
