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: 667

Add a Comment

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

Join The World of DB2

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

User Groups

Blog Posts

Enhancements to Insert Algorithm 2

Posted by Eric Radzinski on March 13, 2019 at 19:51 0 Comments

Db2 for z/OS APAR PH02052 provides some significant enhancements to Insert Algorithm 2, which was introduced in Db2z 12 to provide faster INSERT throughput for unclustered INSERTs on universal table space objects that use the MEMBER CLUSTER…

Continue

Videos

  • Add Videos
  • View All

DB2 for z/OS APARS

© 2019   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service