How Can I Create Comma-Separated Lists of Column Values in DB2 Using LISTAGG?

Generating Comma-Separated Lists in DB2 SQL Using LISTAGG

The DB2 LISTAGG function simplifies the creation of comma-separated lists of column values, particularly when dealing with grouped data. This function efficiently concatenates values from a specified column for rows sharing the same grouping criteria, using a custom delimiter.

Let's illustrate with a table containing ID and Role columns, where an ID might have multiple associated Role values. To generate a comma-separated list of roles for each unique ID, use this SQL query:

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

Example Data:

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

Result:

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

The ORDER BY clause within LISTAGG controls the order of concatenated values. ORDER BY Role ASC arranges them ascendingly; ORDER BY Role DESC would arrange them descendingly. Omitting ORDER BY results in an undefined order.

The above is the detailed content of How Can I Create Comma-Separated Lists of Column Values in DB2 Using LISTAGG?. For more information, please follow other related articles on the PHP Chinese website!