Oracle: select items in CSV format?

Question

Suppose I had a query

select name from tbl where dept = 123

which returns a data set like this

bob
carol
steve

Is there a way of natively expressing (in sql, or perhaps a procedure?) a query which will return the data as a CSV or space separated list?

bob,carol,steve

Answer 1

What version of Oracle?

Tim Hall has a page describing various string aggregation techniques in Oracle. If you are using Oracle 11.2, the simplest option is

SELECT listagg(name, ',') within group( order by name)
  FROM table_name
 WHERE dept = 123

If you are using earlier versions of Oracle, there are many other approaches with various advantages and disadvantages. Tim's comparison of the various approaches is pretty complete.