Concatenate and group multiple rows in Oracle [duplicate]

Question

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.

Answer 1

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