How to group and concatenate results from multiple records?

Question

I have a query like:

select a.id, b.value from a
left join b
on a.name = b.name
where a.id = 12345;

And I get results like

id value
--------
12345 value1
12345 value2

However I would like to get a result like this:

id value
--------
12345 value1,value2

which concatenates all values group by id by comma.

I have no idea how to achieve this. Any hint will be helpful.

Answer 1

You are looking for listagg():

select a.id, listagg(b.value, ',') within group (order by b.value)
from a left join
     b
     on a.name = b.name
where a.id = 12345
group by a.id;