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!