本文介绍了等效于PostgreSQL的阵列()/ array_to_string()函数在Oracle 9i中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我希望用逗号分隔的值列表从返回甲骨文中多行,返回的行基本上压扁成单列的查询返回一行。

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中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-22 23:24