Home > Database > Mysql Tutorial > How to Generate Comma-Separated Lists in DB2 by Group?

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

Barbara Streisand
Release: 2025-01-09 14:41:43
Original
132 people have browsed it

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>
Copy after login

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>
Copy after login

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>
Copy after login

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>
Copy after login

Result:

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

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template