相当于 Oracle 9i 中的 PostgreSQL array() / array_to_string() 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4326868/
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
Equivalent to PostgreSQL array() / array_to_string() functions in Oracle 9i
提问by walkermatt
I'm hoping to return a single row with a comma separated list of values from a query that returns multiple rows in Oracle, essentially flattening the returned rows into a single row.
我希望从在 Oracle 中返回多行的查询返回带有逗号分隔值列表的单行，基本上将返回的行展平为单行。
In PostgreSQL this can be achieved using the array and array_to_string functions like this:
在 PostgreSQL 中，这可以使用 array 和 array_to_string 函数来实现，如下所示：
Given the table "people":
鉴于表“人”：
id | name
---------
1 | bob
2 | alice
3 | jon
The SQL:
SQL：
select array_to_string(array(select name from people), ',') as names;
Will return:
将返回：
names
-------------
bob,alice,jon
How would I achieve the same result in Oracle 9i?
我将如何在 Oracle 9i 中获得相同的结果？
Thanks,
谢谢，
Matt
马特
采纳答案by Justin Cave
Tim Hall has the definitive collection of string aggregation techniques in Oracle.
Tim Hall在 Oracle 中拥有权威的字符串聚合技术集合。
If you're stuck on 9i, my personal preference would be to define a custom aggregate (there is an implementation of string_agg on that page) such that you would have
如果您坚持使用 9i，我个人的偏好是定义一个自定义聚合（该页面上有一个 string_agg 实现），这样您就可以
SELECT string_agg( name )
FROM people
But you have to define a new STRING_AGG function. If you need to avoid creating new objects, there are other approaches but in 9i they're going to be messier than the PostgreSQL syntax.
但是您必须定义一个新的 STRING_AGG 函数。如果您需要避免创建新对象，还有其他方法，但在 9i 中它们将比 PostgreSQL 语法更混乱。
回答by Dan
In 10g I definitely prefer the COLLECT option mentioned at the end of Tim's article.
在 10g 中，我绝对更喜欢 Tim 文章末尾提到的 COLLECT 选项。
The nice thing about that approach is that the same underlying function (that accepts the collection as an argument), can be used both as an aggregate and as a multiset function:
这种方法的好处是相同的底层函数（接受集合作为参数），既可以用作聚合函数，也可以用作多集函数：
SELECT deptno, tab_to_string(CAST(MULTISET(SELECT ename FROM emp
WHERE deptno = dept.deptno) AS t_varchar2_tab), ',') FROM dept
However in 9i that's not available. SYS_CONNECT_BY_PATH is nice because it's flexible, but it can be slow, so be careful of that.
但是在 9i 中不可用。SYS_CONNECT_BY_PATH 很好，因为它很灵活，但它可能很慢，所以要小心。
