本文介绍了如何在不创建存储过程的情况下在 Oracle 中将多行连接为一个?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
如何在不创建存储过程的情况下在oracle中实现以下功能?
How can I achieve the following in oracle without creating a stored procedure?
数据集:
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
推荐答案
进行字符串聚合的方法有很多，但最简单的是用户定义函数.试试这个不需要函数的方法. 作为注释，没有功能就没有简单的方法.
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.
这是没有自定义函数的最短路径:(它使用 ROW_NUMBER() 和 SYS_CONNECT_BY_PATH 函数)
This is the shortest route without a custom function: (it uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions )
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;
这篇关于如何在不创建存储过程的情况下在 Oracle 中将多行连接为一个?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！