How Can LISTAGG or WM_CONCAT Combine Multiple Database Rows into a Single Comma-Separated List?

Aggregating Multiple Database Rows into a Single Comma-Delimited String

Database tasks often require consolidating multiple rows into a single, comma-separated list. Oracle databases provide efficient solutions for this using the LISTAGG or WM_CONCAT functions. While WM_CONCAT is deprecated, LISTAGG (available from Oracle 11.2 onwards) offers superior flexibility and is the recommended approach. Both functions achieve the same basic result: combining rows into a delimited string.

Consider a query retrieving country names:

<code class="language-sql">SELECT * FROM countries;</code>

To generate a comma-separated list of these names, use LISTAGG:

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

This returns a single row containing all country names, neatly separated by commas and ordered alphabetically.

For databases supporting the deprecated WM_CONCAT, the equivalent would be:

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

While both produce similar output, LISTAGG offers advantages. It allows for customization of the delimiter and provides control over the order of aggregated values. Consult your database documentation for detailed LISTAGG function parameters.

The above is the detailed content of How Can LISTAGG or WM_CONCAT Combine Multiple Database Rows into a Single Comma-Separated List?. For more information, please follow other related articles on the PHP Chinese website!