List groups of values with the LISTAGG built-in function in Db2 for z/OS
Paul_McWilliams 110000JT36 Visits (11091)
In Db2 12 Func
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.
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.
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.
Functional code for the LISTAGG built-in function was delivered in APAR PI73929.