How to Generate Comma-Separated Values from Grouped Data in DB2 SQL?

DB2 SQL grouped data generates comma separated values

Question: Is there a function in DB2 SQL that can directly generate comma separated values ​​of grouped column data?

Scenario: We have a table with ID column and Role column. There may be multiple rows for the same ID, with each row representing a different role for that ID. The goal is to concatenate these roles into a comma separated string for each unique ID.

Example:

<code>ID   | Role
------------
4555 | 2
4555 | 3
4555 | 4</code>

Expected output:

<code>4555 2,3,4</code>

Answer:

DB2 LUW 9.7 introduces a new function LITAGG that solves this problem.

Grammar:

<code>LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY expression ASC|DESC)</code>

Instructions:

• expression: Column to join
• delimiter: The delimiter used between concatenated values ​​(e.g.,,)

Usage:

To retrieve the comma separated role values ​​for each unique ID, you can use the following query:

<code>SELECT id, LISTAGG(role, ', ') WITHIN GROUP (ORDER BY role ASC) AS roles
FROM myTable
GROUP BY id;</code>

Output:

<code>ID   | roles
------------
4555 | 2,3,4</code>

Additional notes:

• The ORDER BY clause in the LISTAGG function determines the order of joins.
• If no ORDER BY clause is specified, the values ​​will be concatenated in any order.
The
• LISTAGG function can also be used to generate other types of connection strings, such as newline-separated values.

The above is the detailed content of How to Generate Comma-Separated Values from Grouped Data in DB2 SQL?. For more information, please follow other related articles on the PHP Chinese website!