Oracle DB, concat all values from 1 column using "," separator

Question

I have Oracle DB table with 1 column UUID

How can I concatenate all the UUID values in one string separated by "," so the output will be like

"id1","id2","id3","id4"....

Answer 1

You can use listagg():

select listagg(uuid, ',') within group(order by uuid) uuids
from mytable

This will give you a single record with a single column containing all uuids, separated with a comma.

Note that listagg() returns a varchar, hence the output cannot be more than 4000 bytes.

Also note that:

• the comma is the default separator, so in your case it is not strictly necessary to pass a second argument to the function

• I added an order by clause in order to generate a deterministic result: this option is not mandatory, so if the ordering of items in the list does not matter to you then you can leave it apart (in which case you will get the uuids in an undefined order)

Answer 2

You can use listagg() -- assuming there are not too many values. The maximum string size is 4000 bytes.

selest '"' || listagg(uuid, '","') within group (order by uuid) || '"'