How Can I Concatenate and Group Rows in Oracle?

Concatenating and Grouping Multiple Rows in Oracle

In Oracle, the task of concatenating records based on a grouping condition can be challenging. Consider a table like this:

NAME          GROUP_NAME
name1         groupA
name2         groupB
name5         groupC
name4         groupA
name3         groupC

To achieve the desired result of grouping and concatenating the "NAME" column based on "GROUP_NAME", you can leverage the LISTAGG function in Oracle 11g or later:

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

However, if you are working with an Oracle version that does not support LISTAGG, there are alternative methods. One approach utilizes analytical functions:

select grp,
    ltrim(max(sys_connect_by_path
       (name, ',' )), ',') scbp
  from (select name, grp,
            row_number() over
           (partition by grp
            order by name) rn
         from tab
          )
start with rn = 1
connect by prior rn = rn-1
and prior grp = grp
  group by grp
  order by grp

By combining these techniques, you can effectively concatenate and group multiple rows in Oracle, even in the absence of LISTAGG.

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