相当于 Oracle 9i 中的 PostgreSQL array()/array_to_string() 函数
我希望从在 Oracle 中返回多行的查询中返回带有逗号分隔值列表的单行，实质上是将返回的行展平为单行.
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.
在 PostgreSQL 中，这可以使用数组和 array_to_string 函数来实现，如下所示:
In PostgreSQL this can be achieved using the array and array_to_string functions like this:
给定表人":
id | name
---------
1 | bob
2 | alice
3 | jon
SQL:
select array_to_string(array(select name from people), ',') as names;
将返回:
names
-------------
bob,alice,jon
如何在 Oracle 9i 中获得相同的结果?
How would I achieve the same result in Oracle 9i?
谢谢，
马特
推荐答案
Tim Hall 拥有 Oracle 中的字符串聚合技术.
Tim Hall has the definitive collection of string aggregation techniques in Oracle.
如果您坚持使用 9i，我个人的偏好是定义一个自定义聚合(该页面上有 string_agg 的实现)，这样您就可以
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
SELECT string_agg( name )
FROM people
但是你必须定义一个新的 STRING_AGG 函数.如果您需要避免创建新对象，还有其他方法，但在 9i 中它们会比 PostgreSQL 语法更混乱.
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.
相关文章