问题描述
我希望用逗号分隔的值列表从返回甲骨文中多行,返回的行基本上压扁成单列的查询返回一行。
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?
谢谢,
太
推荐答案
蒂姆·霍尔具有的。
如果你被困在9i中,我个人的preference将定义一个自定义聚合(有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.
这篇关于等效于PostgreSQL的阵列()/ array_to_string()函数在Oracle 9i中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!