String aggregation in Oracle

110 Views Asked by At

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?

1

There are 1 best solutions below

2
On
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