如何在不创建存储过程的情况下在 Oracle 中将多行连接为一个?

2021-12-05 00:00:00 sql concatenation oracle string-aggregation

如何在不创建存储过程的情况下在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;

相关文章