Db2 LISTAGG

Summary: in this tutorial, you will learn how to use the Db2 LISTAGG() function to aggregate a set of strings into a single string by concatenating strings.

Introduction to Db2 LISTAGG() function

The LISTAGG() function aggregates a set of strings into a single string by concatenating the strings with an optional separator between strings.

The basic syntax of the LISTAGG() function is the following:

LISTAGG( ALL | DISTINCT string_expression, separator)
    WITHIN GROUP (ORDER BY sort_expression ASC | DESC)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

 string_expression

Specifies strings to aggregate. The string_expression must be VARCHAR, or can be converted to VARCHAR.

 separator

Specifies the separator between strings for concatenation.

 WITHIN GROUP

Indicates that the aggregation follows a specified ordering within the grouping set.

 ORDER BY

Specifies the order of rows for each grouping set in the aggregation.

The DISTINCT option instructs the LISTAGG() function to aggregate distinct values only. On the other hand, the ALL option allows the LISTAGG() function to aggregate all values, including duplicates.

The LISTAGG() function uses ALL option by default.

Db2 LISTAGG() function example

We’ll use the books table from the sample database to demonstrate the LISTAGG() function.

1) Using Db2 LISTAGG() function to aggregate authors by books example

This example uses LISTAGG() function to find all authors of every book in the books table:

SELECT 
    b.title, 
    LISTAGG(a.first_name || ' ' || a.last_name,',') author_list
FROM 
    books b
    INNER JOIN book_authors ba 
        ON ba.book_id = b.book_id
    INNER JOIN authors a 
        ON a.author_id = ba.author_id
GROUP BY 
    title;  
Code language: SQL (Structured Query Language) (sql)

This picture shows the partial result set:

2) Using Db2 LISTAGG() function with WITHIN GROUP clause example

To sort the authors by the first names, you add the WITHIN GROUP clause as shown in the following query:

SELECT 
    b.title, 
    LISTAGG(a.first_name || ' ' || a.last_name,',')
    WITHIN GROUP (ORDER BY a.first_name) author_list
FROM 
    books b
    INNER JOIN book_authors ba 
        ON ba.book_id = b.book_id
    INNER JOIN authors a 
        ON a.author_id = ba.author_id
GROUP BY 
    title;    
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

In this tutorial, you have learned how to use the Db2 LISTAGG() function to aggregate a set of strings into a single string by concatenating strings.