Equivalent to PostgreSQL array() / array_to_string() functions in Oracle 9i

Question

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

Answer 1

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.

Answer 2

In 10g I definitely prefer the COLLECT option mentioned at the end of Tim's article.

The nice thing about that approach is that the same underlying function (that accepts the collection as an argument), can be used both as an aggregate and as a multiset function:

SELECT deptno, tab_to_string(CAST(MULTISET(SELECT ename FROM emp 
WHERE deptno = dept.deptno) AS t_varchar2_tab), ',') FROM dept

However in 9i that's not available. SYS_CONNECT_BY_PATH is nice because it's flexible, but it can be slow, so be careful of that.