In Oracle, we can use the LISTAGG() function to convert our query results to a comma separated list.

So, instead of each value being output in a separate row, all values are output in a single row, separated by a comma (or some other delimiter of our choosing).

Example

Imagine we run the following query:

SELECT last_name 
FROM employees
WHERE job_id = 'IT_PROG';

Result:

   LAST_NAME 
____________ 
Hunold       
Ernst        
Austin       
Pataballa    
Lorentz      

That query returned five rows, each with a different value.

If we want those values to be output on a single row, we can do the following:

SELECT LISTAGG(last_name, ', ')
FROM employees
WHERE job_id = 'IT_PROG';

Result:

                      LISTAGG(LAST_NAME,',') 
____________________________________________ 
Hunold, Ernst, Austin, Pataballa, Lorentz    

All we did was pass the column name to the LISTAGG() function, as well as our chosen delimiter.

We can use a different delimiter or we could omit that argument altogether so that all items are concatenated.

The function also accepts a DISTINCT clause (to remove duplicate values), and an ORDER BY clause (to order the output of the function).

The function can also be quite handy when grouping query results.

See LISTAGG() Function in Oracle for more examples.