How to Combine Multiple Rows into a Comma-Delimited List in Oracle?

Aggregating Rows into Comma-Delimited Lists in Oracle

Oracle offers several functions to consolidate multiple rows into a single string with comma-separated values. This is a common task for data aggregation and report generation. The most prominent functions are WM_CONCAT and LISTAGG.

Using WM_CONCAT

Available in Oracle versions prior to 11.2, WM_CONCAT concatenates values from multiple rows using a specified delimiter. For instance, to generate a comma-separated list of table names within a schema:

<code class="language-sql">SELECT WM_CONCAT(table_name)
FROM user_tables;</code>

Employing LISTAGG

Introduced in Oracle 11.2, LISTAGG provides enhanced capabilities over WM_CONCAT. It allows for greater control, including specifying delimiters and handling NULL values. The following example creates a comma-separated list of table names, omitting NULL entries:

<code class="language-sql">SELECT LISTAGG(table_name, ', ') WITHIN GROUP (ORDER BY table_name)
FROM user_tables;</code>

Practical Application

Imagine a query retrieving multiple citizenship records for each individual. To avoid redundant entries, LISTAGG can be used as a subquery to generate a single comma-separated citizenship list per person:

<code class="language-sql">SELECT person_id,
       (SELECT LISTAGG(citizenship, ', ') WITHIN GROUP (ORDER BY citizenship)
        FROM citizenship
        WHERE person_id = t.person_id) AS citizenship_list
FROM person t;</code>

This returns a single row per person, with a comma-delimited string of their citizenships in the citizenship_list column.

Summary

WM_CONCAT and LISTAGG provide robust and versatile ways to aggregate multiple rows into comma-separated lists in Oracle. These functions streamline data manipulation and improve the clarity of SQL queries.

The above is the detailed content of How to Combine Multiple Rows into a Comma-Delimited List in Oracle?. For more information, please follow other related articles on the PHP Chinese website!