String aggregation in Oracle

Question

I new to oracle SQL.I want to get the output like this

    deptno         ename
    10               A
    20               b    
    30               c
    10               d
    10               e
    30               f

as

     deptno                 ename
       10                        a,d,e
       20                        b
       30                        c , f

in single select statement?

check out the `listagg()` function in the manual. – Jun 17 '15 at 07:52 — , Jun 17 '15 at 07:52

score 0 · Answer 1 · answered Jun 17 '15 at 08:07

0

select deptno, listagg (lower(ename),', ') within group (order by ename) ename from
(
select 10 deptno,'A' ename from dual union all
select 20 deptno,'b' ename from dual union all
select 30 deptno,'c' ename from dual union all
select 10 deptno,'d' ename from dual union all
select 10 deptno,'e' ename from dual union all
select 30 deptno,'f' ename from dual   
)
group by deptno

answered Jun 17 '15 at 08:07

ksa

403
1
5
15

Thanks for the answer, but I want inside select query to be generic..could you pls help me – deepa kulkarni Jun 17 '15 at 08:41
select deptno, listagg (lower(ename),', ') within group (order by ename) ename from %your_table% group by deptno – ksa Jun 17 '15 at 08:54
Thanks a Lot Guys!!!! – deepa kulkarni Jun 18 '15 at 06:28

String aggregation in Oracle

1 Answers1