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

Consolidating Multiple Rows into a Single Row in Oracle SQL

Oracle SQL offers several methods for combining multiple rows into a single row. One approach uses the WM_CONCAT function (note: deprecated in Oracle 12c and later). WM_CONCAT concatenates values from multiple rows within a specific column:

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

For Oracle versions where WM_CONCAT is not available, a custom aggregate function provides a viable solution. Detailed guidance on creating such a function for string aggregation can be found on resources like Oracle-base.com. A basic example:

<code class="language-sql">CREATE FUNCTION String_Agg(VALUES VARCHAR2, DELIM VARCHAR2) RETURN VARCHAR2;
-- Function implementation details here</code>

This custom function allows for string value aggregation:

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

Both WM_CONCAT (where applicable) and custom aggregate functions effectively consolidate multiple rows into a single row, presenting data in a more concise and organized manner.

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