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.
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.