How Can I Concatenate Column Values in DB2 Groups with Comma Separators?

DB2: Generate comma separated group values ​​

This article explores whether there is a built-in function in DB2 SQL that can concatenate column values ​​within groups and separate them with commas. The need for this function arises in situations where multiple rows share the same group identifier (e.g. ID in the example) but other columns have different values ​​(e.g. Role). The desired output is to concatenate the values ​​associated with each group into a comma separated list.

Solution: Use the LISTAGG function

DB2 LUW 9.7 introduces the LISTAGG function, which can concatenate the values ​​of specified columns in each group, separated by a user-defined delimiter. This function can be used to achieve the desired comma separated output.

Example:

Consider the following myTable with id and category columns:

<code class="language-sql">create table myTable (id int, category int);

insert into myTable values (1, 1);
insert into myTable values (2, 2);
insert into myTable values (5, 1);
insert into myTable values (3, 1);
insert into myTable values (4, 2);</code>

To generate a comma separated list of id values ​​in each category group, you can use the following query:

<code class="language-sql">select category, LISTAGG(id, ', ') as ids from myTable group by category;</code>

This query will produce the following output:

<code>CATEGORY  IDS
--------- -----
1         1, 5, 3
2         2, 4</code>

Additional features:

The LISTAGG function allows additional control over the order of connected values ​​within each group. You can control the sort order of values ​​before joining by specifying the ORDER BY clause in the LISTAGG function.

For example, the following query will sort the id values ​​in each category group in ascending order:

<code class="language-sql">select category, LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as ids from myTable group by category;</code>

This will produce the following output:

<code>CATEGORY  IDS
--------- -----
1         1, 3, 5
2         2, 4</code>

The above is the detailed content of How Can I Concatenate Column Values in DB2 Groups with Comma Separators?. For more information, please follow other related articles on the PHP Chinese website!