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.

Category >> Others  
If you want someone to read your code, please put the code inside <pre><code> and </code></pre> tags. For example:
String foo = "bar";
  • MC Benton

    This is exactly what I’m trying to do, but Dbvisualizer gives me a “SQL command not properly ended” error. There are about 30 different fields including some placeholder constants and several joins.