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
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.
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
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