Grouping values by using the LISTAGG function

About LISTAGG

The LISTAGG function is used to aggregate a set of string values within a group into a single string by appending the string-expression values based on the order that's specified in the 'WITHIN GROUP' clause.

• As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
• As a group-set aggregate function, LISTAGG operates on and returns an output row for each group that's defined by GROUP BY clause.

The following diagram shows the syntax for LISTAGG:

The input, string-expression, must be a string (character, graphic, or binary), a numeric data type, or a LOB. The numeric argument and LOBs are supported via implicit casting (numeric to VARCHAR; CLOB to VARCHAR of max length of 32704; DBCLOB to VARGRAPHIC of max length of 16352; BLOB to VARBINARY of max length of 32704).

The separator can be a literal, special register, variable, or an expression that is based on literals, special registers, or variables, as long as the expression does not include a non-deterministic function or a function that takes an external action.

The result data type of LISTAGG is determined by the input data type summarized in the following table:

Example: Using LISTAGG to output lists of employees who are in the same departments

Suppose you have a table called EMPLS that contains the employee name, employee ID, department ID, and year of birth of the employee.

DROP TABLE EMPLS;

CREATE TABLE EMPLS
(Dept_ID INT,
  E_Name VARCHAR(20),
  EMP_ID INT,
  Birth INT);

INSERT INTO EMPLS VALUES(10, 'Jack', 0012, 1983);
INSERT INTO EMPLS VALUES(10, 'Lily', 0015, 1990);
INSERT INTO EMPLS VALUES(20, 'Tom', 0019, 1983);
INSERT INTO EMPLS VALUES(20, 'Bob', 0022, 1976);
INSERT INTO EMPLS VALUES(20, 'Frank', 0004, 1983);
INSERT INTO EMPLS VALUES(20, 'Tom', 0014, 1985);
INSERT INTO EMPLS VALUES(30, 'Jerry', 0028, 1991);
INSERT INTO EMPLS VALUES(30, 'Chris', 0021, 1981);
INSERT INTO EMPLS VALUES(30, 'Jill', 0002, 1984);
INSERT INTO EMPLS VALUES(30, 'Jerry', 0031, 1984);
INSERT INTO EMPLS VALUES(30, 'Allan', 0006, 1995);

Now suppose that you want to output the names of all the employees who are in the same department into a single row in ascending order according to their employee ID and birth date from this table.

One option is to first define a cursor to query the employees' names and departments from the table. You could then fetch data from the cursor and use application logic to identify employees who are in the same department and then concatenate their names. This approach is absolutely possible, but requires a lot of extra effort.

Another possible option is represented in the following SQLPL (note that # represents a SQL terminator here):

DROP TABLE RESULT#
CREATE TABLE RESULT(DEPT_ID INT, EMP_NAMES VARCHAR(100))#
DROP PROCEDURE AggEmpName#
CREATE PROCEDURE AggEmpName ()
    BEGIN
       DECLARE STMT VARCHAR(50) DEFAULT 'No name'; 

       DECLARE PRE_Dept_ID INT;
       SET STMT= '';
       SET PRE_Dept_ID= 0;
    
       FOR MYC CURSOR FOR
          SELECT E_Name, Dept_ID
          FROM EMPLS
          ORDER BY Dept_ID, Birth, Emp_ID
          DO
             IF PRE_Dept_ID = 0 THEN
               SET PRE_Dept_ID = Dept_ID;
               SET STMT = STMT ||E_Name||';';
          ELSE
             IF PRE_Dept_ID = Dept_ID THEN
               SET STMT = STMT ||E_Name||';';
             ELSE
                INSERT INTO RESULT VALUES (PRE_Dept_ID,STMT);
                SET PRE_Dept_ID = Dept_ID;
                SET STMT= '';
                SET STMT = STMT ||E_Name||';';
              END IF;
          END IF;
       END FOR;
  INSERT INTO RESULT VALUES (PRE_Dept_ID,STMT);
  END# 

CALL AggEmpName () #

SELECT * FROM RESULT#

First, you create a RESULT table to store the result (employee names that belong to the same department). Inside the body of SQLPL(AggEmpName), you concatenate the employee names for the same department in STMT. When the department ID changes, you insert the concatenated string to the RESULT table with the department ID. Then you reset the STMT and start to concatenate the name for the next department. Here's our result:

DEPT_ID     EMP_NAMES

10          Jack;Lily;
20          Bob;Frank;Tom;Tom;
30          Chris;Jill;Jerry;Jerry;Allan;
   3 record(s) selected

With the LISTAGG function, this task becomes very simple. Consider the following query:

SELECT Dept_ID,
       LISTAGG(ALL E_Name, '; ')
         WITHIN GROUP (ORDER BY Birth, Emp_ID) 
        AS Name_List
  FROM EMPLS
  GROUP BY Dept_ID;

The syntax is easy to understand. In each department (GROUP BY Dept_ID), you aggregate the employee names, and order by Birth and Emp_ID, as shown in the following result:

Example: Removing duplicate employee names from each department group created by LISTAGG

If you want to remove the duplicate names in each group, you can use the DISTINCT keyword, as shown in the following query:

SELECT Dept_ID,
       LISTAGG(DISTINCT E_Name, '; ')
         WITHIN GROUP (ORDER BY E_Name, Birth, Emp_ID) 
        AS Name_List
 FROM EMPLS
 GROUP BY Dept_ID;

As you might notice, it's very easy to use and maintain (when comparing with implementing your own application logic for doing so).

SELECT Dept_ID,
    LISTAGG(DISTINCT  E_Name, '; ')
      WITHIN GROUP (ORDER BY  Birth, Emp_ID)
        AS Name_List
 FROM EMPLS
GROUP BY Dept_ID;

-214, ERROR: AN EXPRESSION IN THE FOLLOWING POSITION, OR STARTING WITH 1 IN THE ORDER BY CLAUSE IS NOT

VALID. REASON CODE = 8

RC 8 DISTINCT is specified in the LISTAGG function and the first argument expression cannot be matched exactly
with the first sort key expression of the ORDER BY specified in the WITHIN GROUP clause. This reason code occurs 
only when clause-type is ORDER BY.

This rule is required to avoid non-deterministic results.

Now suppose that our table contains the following data (some columns are not shown here). Notice that there are two employees named Tom, each with a different year of birth.

Jill, Tom, Bob

Jill, Bob, Tom

Without the restriction of -214, Db2® cannot be sure which row (Tom 1983 or Tom 1990) is duplicated and needs to be removed.

Example: Ordering the rows in LISTAGG output by grouping column value

FL 504 Suppose that table EMPL contains the following columns:

You want to retrieve a result table in which each row represents a department, and a list of employees in that department. You also want the result table rows to be ordered by department ID, in descending order. Use the following query:

SELECT Dept_ID,
     LISTAGG(ALL E_Name, '; ')
          WITHIN GROUP (ORDER BY Birth, Emp_ID)
                AS Name_List
  FROM EMPLS
 GROUP BY Dept_ID
 ORDER BY Dept_ID DESC;

The result table looks like this one:

Comparing LISTAGG and XMLAGG

If you are familiar with XML, you might already know about the aggregate function in XML called XMLAGG. XMLAGG was introduced in Db2 9 for z/OS.

To achieve the same (or similar) results, the closest you can do with XMLAGG is:

SELECT DEPT_ID,
       XMLAGG(XMLTEXT(E_NAME || '; ')
           ORDER BY Birth, EMP_ID)     
       AS Name_List
FROM EMPLS
GROUP BY Dept_ID;

XMLAGG takes only the XML data type as input, so you need to apply XMLTEXT() on E_NAME, then concatenate with a ';' as a separator. Here's the result:

DEPT_ID     NAME_LIST
10          Jack; Lily;
20          Bob; Frank; Tom; Tom;
30          Chris; Jill; Jerry; Jerry; Allan;       
 3 record(s) selected