Convert Results to Comma-delimited List in Oracle
Let’s say you are running a query and that there is a one-to-many relationship between one of the columns in one table and a column in another table. You could always retrieve the data from the database and process it using a regular programming language, but sometimes you want to do it all on the database side. Let’s say you wanted to get all the values corresponding to a key in the first table, convert them to a comma-delimited string on the fly, and return the results that way.
Turns out there are a few ways to do this, but none of them are ideal (that I could find). But I had to search all over the Web to find an article that explains it, and this is the best one I could find. It shows various approaches to doing it, including pros and cons to each of them.
See http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php.