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.
If we want to select all values tagged with "start" and "end", as shown in the following, how to write the sql query?
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.
<pre><code> String foo = "bar"; </code></pre>