How to Generate Comma-Separated Lists in DB2 by Group?

Generating Comma-Separated Lists in DB2 using LISTAGG

This guide demonstrates how to create comma-separated lists of column values grouped by another column in DB2. The LISTAGG function is key to achieving this.

Scenario:

Imagine a table with ID and Role columns, where each ID can have multiple Roles. The goal is to output a comma-separated list of roles for each unique ID.

Example Data:

ID	Role
4555	2
4555	3
4555	4

Desired Output:

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

Solution using LISTAGG:

DB2's LISTAGG function efficiently concatenates values within a group. The syntax is:

<code class="language-sql">LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY column_name) OVER (PARTITION BY group_column)</code>

Example Query:

Let's use a sample table named 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 get comma-separated id values for each category:

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

Result:

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

This query groups rows by category, orders the id values within each group, and then uses LISTAGG to concatenate them into a single comma-separated string. The result shows the comma-separated list of IDs for each category. This approach effectively solves the problem of generating comma-separated lists based on grouping in DB2.

The above is the detailed content of How to Generate Comma-Separated Lists in DB2 by Group?. For more information, please follow other related articles on the PHP Chinese website!