How Can I Combine Multiple Rows into a Comma-Separated List in Oracle?

Merge multiple rows into comma separated list in Oracle database

When working with data in an Oracle database, it is sometimes necessary to combine multiple rows of data into a single comma-separated value (CSV) list. You can use built-in functions such as WM_CONCAT or LISTAGG to achieve this task.

The WM_CONCAT function, available in Oracle databases prior to version 11.2, provides a convenient way to concatenate values. The following query demonstrates its usage:

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

This query retrieves a comma separated list of all table names in the current schema.

Alternatively, the LISTAGG function introduced in Oracle 11.2 provides greater flexibility and control. It allows specifying delimiters and sorting the concatenated values. Here's an example:

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

This query sorts the table names in ascending order before concatenating them using commas as delimiters.

By using WM_CONCAT or LISTAGG, developers can easily merge multiple rows into a single CSV, simplifying data manipulation and improving query performance.

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