How to Aggregate Rows into Comma-Separated Lists in Oracle?

Oracle: Combining Rows into Comma-Separated Lists

Frequently, data manipulation requires aggregating multiple rows into a single, comma-delimited string. Oracle offers several methods to accomplish this:

Method 1: LISTAGG (Oracle 11.2 and later)

The LISTAGG function (available from Oracle 11.2 onwards) efficiently concatenates rows using a specified delimiter. Its syntax is:

<code class="language-sql">LISTAGG(expression, delimiter [, separator]) WITHIN GROUP (ORDER BY order_expression)</code>

Example: Generating a comma-separated list of country names from a countries table:

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

Method 2: WM_CONCAT (Oracle versions prior to 11.2)

For older Oracle databases (before 11.2), the WM_CONCAT function provides a similar capability:

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

Method 3: Custom PL/SQL Function

Should neither LISTAGG nor WM_CONCAT suffice, a custom PL/SQL function offers a flexible solution. This allows for tailored concatenation logic, as demonstrated in the example provided using loops and string concatenation.

The above is the detailed content of How to Aggregate Rows into Comma-Separated Lists in Oracle?. For more information, please follow other related articles on the PHP Chinese website!