Use LISTAGG() on multiple joined table in Oracle

You may know how to use LISTAGG() on a single table, but don’t know how to use LISTAGG on multiple joined tables. This example demonstrates how to use aggregate function on joined multiple tables in Oracle 12g.

Assuming we have the following two tables.

“User” Table

ID	Name
111	aaa
222	bbb
333	ccc

“Record” Table

ID	Tag	Value
111	start	1
111	mid	2
111	end	3
222	start	1
222	end	2
333	start	1
333	mid	2
333	end	3

If we want to select all values tagged with “start” and “end”, as shown in the following, how to write the sql query?

Expected Result:

ID	Name	AggValues
111	aaa	1,3
222	bbb	1,2
333	ccc	1,3

select u.ID, u.Name, listagg(Value, ', ') within group (order by Tag) as AggValues
from User u 
left outer join Record r
on u.ID = r.ID and r.Tag in ('start', 'end')
group by u.ID, u.Name;

A common mistake is missing fields in the last group by clause. Every select field should be also in the group by clause. In this case, we select select ID and Name, so ID and Name should be used to group the results.