SQL oracle中是否有类似于mysql中的group_concat的功能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16771086/
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
Is there any function in oracle similar to group_concat in mysql?
提问by Prashanth
My inputs are in this way.
我的输入是这样的。
col1 col2
1 a
1 b
2 c
2 d
2 e
O/p: Should Be like
O/p：应该像
col1 col2
1 a,b
2 c,d,e
I want a query that can be fired at DB level. I've tried various ways, but wasn't able to make this out...
我想要一个可以在数据库级别触发的查询。我尝试了各种方法，但无法解决这个问题......
回答by hsuk
11g and higher:Use listagg:
11g 及更高版本：使用listagg：
SELECT
col1,
LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) "names"
FROM table_x
GROUP BY col1
10g and lower:One method is to use a function:
10g 及以下：一种方法是使用函数：
CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val in number)
RETURN VARCHAR2
IS
return_text VARCHAR2(10000) := NULL;
BEGIN
FOR x IN (SELECT col2 FROM table_name WHERE col1 = input_val) LOOP
return_text := return_text || ',' || x.col2 ;
END LOOP;
RETURN LTRIM(return_text, ',');
END;
/
To use the function:
要使用该功能：
select col1, get_comma_separated_value(col1) from table_name
Note:There is an (unsupported) function WM_CONCATavailable on certain older versions of Oracle, which might help you out - see here for details.
注意：WM_CONCAT在某些旧版本的 Oracle 上有一个（不受支持的）函数，它可能会对您有所帮助 -有关详细信息，请参见此处。
In MySQL:
在 MySQL 中：
SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1
