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
select array_to_string(array(select name from people), ',') as names;
names ------------- bob,alice,jon
How would I achieve the same result in Oracle 9i?
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.