How Can I Create Comma-Separated Lists of Values from Groups in DB2?

Generate comma separated output by group in DB2

DB2 built-in function LISTAGG can concatenate column values ​​with the same grouping, separated by commas. This is useful when you need to merge data within groups.

For example, suppose a table contains two columns, ID and Role, and three rows have the same ID but different Role values. The goal is to concatenate the Role values ​​for each ID into a comma-separated list.

The LISTAGG function introduced starting with DB2 LUW 9.7 accomplishes this:

<code class="language-sql">SELECT ID, LISTAGG(Role, ', ') AS Roles
FROM myTable
GROUP BY ID;</code>

This query will generate the desired output:

<code>ID   Roles
----------
4555 2,3,4</code>

It is worth noting that you can use the WITHIN GROUP clause and the ORDER BY statement to specify the order in which values ​​are concatenated. This allows for greater control over the order in which values ​​are listed:

<code class="language-sql">SELECT ID, LISTAGG(Role, ', ') WITHIN GROUP (ORDER BY Role ASC) AS Roles
FROM myTable
GROUP BY ID;</code>

In this case the output will be:

<code>ID   Roles
----------
4555 2,3,4</code>

LISTAGG Provides a convenient way to aggregate and join data in DB2, enabling efficient group-based string operations.

The above is the detailed content of How Can I Create Comma-Separated Lists of Values from Groups in DB2?. For more information, please follow other related articles on the PHP Chinese website!