Can Oracle SQL Aggregate Multiple Rows into a Single Row?

Oracle SQL: Merge multiple rows of data into one row

Question:

Can Oracle SQL merge multiple rows of data in a table into one row?

Instructions:

Suppose there is a table with the following structure:

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

The goal is to get the following result set:

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

Solution:

Depending on the Oracle SQL version you are using, you can use the following methods:

1. wm_concat() function (Oracle 12c and below):

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

(Note: The wm_concat() function is deprecated in Oracle 12c. It is recommended to use LISTAGG instead.)

2. Custom string aggregation:

If the wm_concat() function is not available, you can implement a custom method to do string aggregation. Please visit oracle-base.com for detailed instructions.

3. Use LISTAGG function (Oracle 11gR2 and above):

This is the recommended modern approach as it is more robust and supported in newer Oracle versions.

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

This query uses the LISTAGG function to concatenate the values ​​in field2 into a string, using spaces as delimiters, and sorting by the value of field2. The WITHIN GROUP (ORDER BY field2) clause ensures the ordering of the values ​​in the resulting string. The GROUP BY field1 clause is grouped according to the value of field1, thereby merging multiple rows into one row.

Choose the solution that best suits your version of Oracle. For Oracle 11gR2 and above, it is strongly recommended to use the LISTAGG function.

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