Home > Database > Mysql Tutorial > How to Aggregate Multiple Rows into One in Oracle SQL?

How to Aggregate Multiple Rows into One in Oracle SQL?

Susan Sarandon
Release: 2025-01-15 18:38:46
Original
829 people have browsed it

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:

1

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

Copy after login

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

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

Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template