How to Aggregate Multiple Rows into One in Oracle SQL?

Oracle SQL: Combine multiple rows of data into one row

When a data set is organized by specific columns, it may be necessary to merge multiple rows into a single row. This can be achieved through SQL's data aggregation operations.

In Oracle SQL, the WM_CONCAT function provides a simple way to join multiple rows of values. However, it is worth noting that this function has been marked as unsupported and removed in Oracle 12c and later.

WM_CONCAT Method (before Oracle 12c)

Prior to Oracle 12c, you could use WM_CONCAT to aggregate values:

SELECT field1, WM_CONCAT(field2) FROM YourTable GROUP BY field1;

Alternative: LISTAGG (Oracle 12c and later)

For Oracle 12c or higher databases, LISTAGG provides an alternative to WM_CONCAT. This function provides enhanced functionality specifically for string aggregation tasks:

SELECT field1, LISTAGG(field2, ',') WITHIN GROUP (ORDER BY field2) FROM YourTable GROUP BY field1;

Custom implementation

If there is neither WM_CONCAT nor LISTAGG, you can create a custom implementation using a combination of subqueries and string manipulation functions. Related links (for example, oracle-base.com) provide detailed instructions for this method.

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