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:
1 |
|
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:
1 |
|
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!