How Can I Concatenate and Group Multiple Rows in Oracle?

Concatenating and Grouping Multiple Rows in Oracle

This article addresses the challenge of concatenating and grouping multiple rows in Oracle, allowing you to transform tables with scattered data into a more organized structure. Consider the following scenario:

You have a table containing two columns, NAME and GROUP_NAME:

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

Your goal is to create a result where names are concatenated for each unique GROUP_NAME value:

GROUP_NAME     NAMES
groupA         name1,name4
groupB         name2
groupC         name3,name5

In this case, the LISTAGG function, available in Oracle 11g and later, provides a straightforward solution:

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

However, if you are working with Oracle versions prior to 11g, you can employ analytics to achieve the same result:

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 leveraging both LISTAGG and analytics, you can efficiently concatenate and group multiple rows in Oracle, transforming your data into a more meaningful representation for further analysis and reporting.

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