How to Aggregate Multiple Rows into a Single Row in Oracle SQL?

Oracle SQL merge multiple rows into a single row

In Oracle database, you may need to merge multiple rows in a table into one row, creating a table structure with multiple columns. Suppose there is a table with the following schema:

<code>A 1
A 2
B 1
B 2</code>

To achieve the following expected results:

<code>A 1 2
B 1 2</code>

Oracle provides specific SQL functions to meet this need:

WM_CONCAT function (deprecated)

Warning: The WM_CONCAT function is now deprecated and has been removed in Oracle 12c and later. Using it in later versions may cause unpredictable results.

<code class="language-sql">SELECT field1, WM_CONCAT(field2)
FROM YourTable
GROUP BY field2;</code>

LISTAGG function

For newer Oracle versions, it is recommended to use the LISTAGG function:

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

Custom aggregation

If neither the WM_CONCAT nor the LISTAGG functions are available in your version of Oracle, you can implement custom aggregations by following these steps:

1. Create a temporary table to store aggregated data.
2. Insert required data into temporary table using loop or recursion.
3. Retrieve aggregate data from a temporary table.

Please note that custom aggregations may be more complex and computationally intensive than using predefined functions.

The above is the detailed content of How to Aggregate Multiple Rows into a Single Row in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!