CODEWITHSUNDEEP
sqloracle-databasecsv
Mark Harrison
Mark Harrison

Reputation: 304434

Oracle: select items in CSV format?

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

Upvotes: 3

Views: 3613

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 7

Related Questions