Concatenate and group multiple rows in Oracle [duplicate]

39
votes

Possible Duplicate:
how to retrieve two columns data in A,B format in Oracle

Suppose I have a table like this:

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

I'd like to have a result like this:

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

If there were only one column in the table, I could concatenate the records by doing the following, but with grouping in the context, I really don't have much idea.

Concatatenating one column table:

SELECT names 
FROM (SELECT SYS_CONNECT_BY_PATH(names,' ') names, level
      FROM name_table

      START WITH names = (SELECT names FROM name_table WHERE rownum = 1)
      CONNECT BY PRIOR names < names
      ORDER BY level DESC)
      WHERE rownum = 1

Updated:

I now have a solution by using LISTAGG:

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

Still interested in a more "general" solution for cases when LISTAGG is not available.

1
sqloracleconcatenationgroupinggroup-concat
What version of Oracle are you using? The options available depend on the Oracle version. – Justin Cave
If you want to use something common, you should write own aggregate function docs.oracle.com/cd/B28359_01/appdev.111/b28425/… can help – Pavel Zimogorov

1 Answers

77
votes

Consider using LISTAGG function in case you're on 11g:

select grp, listagg(name,',') within group( order by name ) 
  from name_table group by grp

sqlFiddle

upd: In case you're not, consider using analytics:

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

sqlFiddle