Equivalent to PostgreSQL array() / array_to_string() functions in 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.

In PostgreSQL this can be achieved using the array and array_to_string functions like this:

Given the table "people":

id | name
---------
1  | bob
2  | alice
3  | jon

The 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?

Thanks,

Matt

Answers


Tim Hall has the definitive collection of string aggregation techniques in 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

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.


Need Your Help

How to stop DrawText from underlining alt characters?

delphi drawing drawtext

I'm using DrawText to draw text onto a DBGrid canvas. The text comes from the database. But when the string contains an ampersand (&) it treats it as an alt shortcut and underlines it.

PHP, run code without opening windows on the webbrowser

php background

If I have a list of elements, and via javascript the user moves the elements in another order, can I, after each move, launch a php code (like a php page) but without having to call it in the brows...