List groups of values with the LISTAGG built-in function in Db2 for z/OS

By Jason Cu and Paul McWilliams

In Db2 12 for z/OS function level 501, you can write efficient queries to select groups of related values and return each group as a list in a single string value, with optional separators, without writing complex recursive SQL statements. Consider the sample EMP table that is supplied with Db2. You can write a query that calls the industry standard LISTAGG built-in function to generate the following result.


---------+---------+---------+---------+---------+---------+---------+--------
WORKDEPT EMPLOYEES
---------+---------+---------+---------+---------+---------+---------+--------
A00 HAAS, HEMMINGER, LUCCHESI, O'CONNELL, ORLANDO
B01 THOMPSON
C01 KWAN, NATZ, NICHOLLS, QUINTANA
D11 ADAMSON, BROWN, JOHN, JONES, LUTZ, PIANKA, SCOUTTEN, STERN, WALKER, YAM...
D21 JEFFERSON, JOHNSON, MARINO, MONTEVERDE, PEREZ, PULASKI, SMITH
E01 GEYER
E11 HENDERSON, PARKER, SCHNEIDER, SCHWARTZ, SETRIGHT, SMITH, SPRINGER
E21 ALONZO, GOUNOT, LEE, MEHTA, SPENSER, WONG

The employee last names are stored as separate string values in the LASTNAME column of each row in the EMP table, as shown in the following excerpt. However, in the LISTAGG result, the employee last names for each department are grouped, or aggregated, into comma-separated lists, and the list for each department is a single string value in the result.


---------+---------+---------+---------+---------+------
SELECT * FROM EMP;
---------+---------+---------+---------+---------+------
EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT
---------+---------+---------+---------+---------+------
000010 CHRISTINE I HAAS A00
000020 MICHAEL L THOMPSON B01
000030 SALLY A KWAN C01
000050 JOHN B GEYER E01
000060 IRVING F STERN D11
000070 EVA D PULASKI D21
000090 EILEEN W HENDERSON E11

You can write SQL statements that return similar aggregated results without calling LISTAGG. However, doing so requires recursion, and including more values in the result requires more recursion. The resulting SQL statement soon becomes quite complex. With LISTAGG, the following SELECT statement produces the example result.

SELECT WORKDEPT,
LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME)
AS EMPLOYEES
FROM EMP
GROUP BY WORKDEPT;

Some Db2 users have also achieved similar results by using the XMLAGG function, which was introduced in Db2 9. However, that workaround requires extra steps, including conversion of non-XML data to XML, a call to the XMLAGG function, and a subsequent call to the REPLACE function, to remove the XML tags from the result.

LISTAGG provides a simple and more scalable solution that also performs better than such alternative approaches. LISTAGG is already supported by Db2 for Linux, UNIX, and Windows, and Db2 for i, and is part of the SQL standard.

Related information
LISTAGG
Function level 501 (activation enabled by APAR PI70535)
Employee table (DSN8C10.EMP)

Views: 1635

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Latest Activity

William Shipley liked Surekha Parekh's blog post Live Db2 12 Client Panel 30th June 2021 - Sign Up today!
yesterday
A blog post by Surekha Parekh was featured
Tuesday
Daniel Luksetich posted a blog post
Monday
Gulfem Ogutgen liked Annette Zawacki's event Garanti-BBVA shares Db2 Analytics Accelerator migration and operational experiences
Monday
Gulfem Ogutgen liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Monday
edmundo nascimento liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Monday
Michael Tiefenbacher posted an event
Thumbnail

30. DeDUG User Group Meeting at virtual

September 24, 2021 from 9am to 1:30pm
Monday
Earl Dugan posted an event

Virtual New England DB2 User's Group Thursday, June 24th, 2021. at Virtual Meeting

June 24, 2021 from 9am to 12pm
Monday
Willy Unger liked Annette Zawacki's event Garanti-BBVA shares Db2 Analytics Accelerator migration and operational experiences
Saturday
carol goldberg liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 17
Adrian Collett liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 17
ONLO posted a discussion
Jun 16
GABRIEL FAJARDO liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 15
Pedro Diaz liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 14
An event by Surekha Parekh was featured
Thumbnail

Interactive Db2 12 Client Panel with John Campbell IBM DE at https://ibm.biz/Db212ClientPanel

June 29, 2021 from 11am to 12:30pm
Jun 14
Carol Davis-Mann posted a blog post
Jun 11

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

ADM10501W - Health indicator "Database Automatic Storage Utilization"

Started by ONLO in Application Development and DB2 Jun 16. 0 Replies

AIX Version 6.1DB2 10.5.6Hi every body,I got the above error:MESSAGE : ADM10501W Health indicator "Database Automatic Storage Utilization" ("db.auto_storage_util") breached the "upper" warning threshold of "80 %" with value "88 %" on "database"…Continue

DB2 Future

Started by Peter E Saxe in Application Development and DB2 May 20. 0 Replies

Has anyone ever submitted ideas of new functions in DBMS software like DB2?   have an idea for a future enhancement to DB2 that I would like to see.  Is this discussion area a good place to discuss this ?  Thanks.Continue

Tags: Enhancements, Future, DB2

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service