Guru: Scalar Use of LISTAGG
August 24, 2020 Ted Holt
The SQL LISTAGG function is as handy as a pocket. Only recently I used it to build a string of comma-separated values (CSV) to populate a drop-down box. It sure beats a cursor and a loop. I’ve noticed in my reading that LISTAGG can be used for both aggregate and scalar purposes under Oracle. Db2 doesn’t support the scalar use, but I found another way to do the same thing.
To see what I’m talking about, check out example 7 in the article Oracle LISTAGG Function Usage, Tips & Examples, by Ben Brumm. I recommend studying the entire article; there’s a lot of good information there.
The IBM Knowledge Center is very clear about the role of LISTAGG in Db2 for i. There’s only one rule on the 7.4 page that describes LISTAGG, and that rule says “LISTAGG cannot be used as part of an OLAP specification.” But that doesn’t mean we can’t do the same sort of thing in a different way.
To illustrate, let’s set up a database of employee information.
declare global temporary table depts (ID dec(3), Name char(30)); insert into session.depts values ( 1, 'Accounting'), ( 2, 'Production'), ( 3, 'Research & Development'), ( 4, 'Quality Assurance'), ( 5, 'Customer Service'), ( 6, 'Purchasing'), ( 7, 'Information Technology'); declare global temporary table emps (ID dec(3), Last char(12), First char(12), DeptID dec(3)); insert into session.emps values ( 1, 'Dover', 'Ben', 2), ( 2, 'Butler', 'Peanut', 7), ( 3, 'Makit', 'Willie', 2), ( 4, 'Wont', 'Betty', 2), ( 5, 'Kake', 'Patty', 7), ( 8, 'De Lion', 'Dan', 1);
Now let’s play with LISTAGG. Here’s a simple query to build a CSV string of all the department names.
select listagg(trim(d.Name),',')
within group(order by d.Name) as list
from session.depts as d
LIST
================================================================
Accounting,Customer Service,Information Technology,Production,Purchasing,Quality Assurance,Research & Development
If that wraps in your browser, please forgive me.
There is no GROUP BY clause, so the entire result set is treated as one group. This is the sort of query I used to load the drop-down box.
Let’s try another one.
select d.Name,
listagg(trim(e.Last),',')
within group(order by e.Last) as Workers
from session.emps as e
join session.depts as d
on e.DeptID = d.ID
group by d.Name, d.ID
order by d.Name
Name Workers
======================= ================
Accounting De Lion
Information Technology Butler,Kake
Production Dover,Makit,Wont
In this example, there is a GROUP BY. LISTAGG retrieves the employees for each department. There are two ORDER BY clauses in this query. The first one sorts the employees of a department by last name. The second one sorts the result set.
Both queries are summary queries. The result set cannot have non-grouping columns (fields). If we want non-grouping columns, we must use a detail query with LISTAGG. Since LISTAGG is not a scalar function, we can embed it in a column, like this:
select e.First, e.Last, e.DeptID,
(select listagg(trim(x.Last),',') within group(order by x.Last)
from session.emps as x
where x.DeptID = e.DeptID) as Workers
from session.emps as e
order by e.Last
First Last DeptID Workers
======== ======== ====== ================
Peanut Butler 7 Butler,Kake
Dan De Lion 1 De Lion
Ben Dover 2 Dover,Makit,Wont
Patty Kake 7 Butler,Kake
Willie Makit 2 Dover,Makit,Wont
Betty Wont 2 Dover,Makit,Wont
The SELECT statement in the fourth column selects all the workers for the department on that row of the result set. EMPS is queried a second time for the department on that row. The result is a detail report with an aggregate in each row.
Depending on the situation, it may make more sense to omit the person on that row from the list.
select e.First, e.Last, e.DeptID,
(select listagg(trim(x.Last),',') within group(order by x.Last)
from session.emps as x
where x.DeptID = e.DeptID
and x.ID <> e.ID) as Coworkers
from session.emps as e
order by e.Last
First Last DeptID Coworkers
======== ======== ====== ==========
Peanut Butler 7 Kake
Dan De Lion 1 (null)
Ben Dover 2 Makit,Wont
Patty Kake 7 Butler
Willie Makit 2 Dover,Wont
Betty Wont 2 Dover,Makit
The query lists the coworkers of the person on each row. Again, the SELECT in the fourth column refers to columns from the selected row and the inner SELECT to join the data.
I can’t say enough good things about LISTAGG. The more I use it, the more I like it.
RELATED STORIES
Oracle LISTAGG Function Usage, Tips & Examples
