Search code examples
sqloracle-databasegroup-bystring-aggregation

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

I have Oracle DB table with 1 column UUID

enter image description here

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

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

  • 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)