Fetch all results in one line csv in Oracle SQL

Question

I am trying to query

select car_name from cars

and the expected output is

| CAR_NAMES                                 |
|'Audi', 'BMW', 'Bugatti', 'Ferrari', 'Ford'| `

How can I do this...

SonalPM · Accepted Answer · 2014-11-07T12:03:22.127

1

Oracle

SELECT listagg (car_name , ',') WITHIN GROUP (ORDER BY car_name )
        car_name 
FROM cars
GROUP BY car_name

MySQL

SELECT 
  GROUP_CONCAT(car_name ) AS CAR_NAMES 
FROM cars

edited Nov 07 '14 at 12:03

answered Nov 07 '14 at 11:55

SonalPM

2

`GROUP_CONCAT` is not available in oracle – Debajyoti Das Nov 07 '14 at 11:56
I want the car name within quotes also `'Audi', 'BMW'....` – Debajyoti Das Nov 07 '14 at 12:07
@DebajyotiDas - You can try this one. This will give you each of your values in single quotes as you expected `select listagg(regexp_replace(car_name ,'^|$',''''),',') within group (order by car_name ) from cars group by car_name;` – arunb2w Nov 07 '14 at 12:39
This works `SELECT listagg( car_name, ', ') within group (order by car_name) car_name from (select '''' || car_name || '''' as car_name from cars)` – Debajyoti Das Nov 07 '14 at 16:36
@DebajyotiDas: So finally You got the answer :) – SonalPM Nov 08 '14 at 05:09

1 Answers1