How to Concatenate and Group Multiple Rows in Oracle?

Concatenate and Group Multiple Rows in Oracle

In a scenario where you have a table with multiple rows that need concatenation and grouping, the use of LISTAGG function in Oracle 11g provides a straightforward solution:

SELECT group_name, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY GROUP) AS "names"
FROM name_table
GROUP BY group_name

The LISTAGG function aggregates multiple values from a group of rows, separating them with a specified delimiter (here, ','). It simplifies the process of concatenating and grouping, producing the desired result.

For databases prior to Oracle 11g, an alternative approach using analytics is available:

SELECT grp, ltrim(max(sys_connect_by_path(name, ',' )), ',') AS scbp
FROM (
    SELECT name, grp, row_number() OVER (PARTITION BY grp ORDER BY name) AS rn
    FROM tab
)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND PRIOR grp = grp
GROUP BY grp
ORDER BY grp

This query leverages the CONNECT BY clause and row_number() analytic function to concatenate and group values iteratively, producing the same output as the LISTAGG method.

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