How to collect values from multiple rows into a single, comma delimited string (using vm_concat ,stragg)

SQL> select deptno, job, ename from emp order by 1,2;
DEPTNO JOB ENAME
---------- --------- ----------
10 PRESIDENT KING
20 ANALYST FORD
20 CLERK ADAMS
20 MANAGER JONES
30 CLERK JAMES
30 MANAGER BLAKE
30 SALESMAN ALLEN

7 rows selected.

SQL> col names format a80 heading "JOB/EMPLOYEE_NAME LIST"
SQL> select deptno, wm_concat(job||','||ename) names
2 from emp
3 group by deptno
4 order by deptno
5 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- --------------------------------------------
10 PRESIDENT,KING
20 ANALYST,FORD,MANAGER,JONES,CLERK,ADAMS
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,ALLEN

3 rows selected.

SQL> select deptno,
2 substr(replace(sys_connect_by_path(name,'/'),'/',','),2) names
3 from ( select deptno, job||','||ename name,
4 row_number() over(partition by deptno order by job, ename) rn
5 from emp
6 )
7 where connect_by_isleaf = 1
8 connect by prior deptno = deptno and prior rn = rn-1
9 start with rn = 1
10 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- ----------------------------------------------------------------------
10 PRESIDENT,KING
20 ANALYST,FORD,CLERK,ADAMS,MANAGER,JONES
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,ALLEN

3 rows selected.

SQL> select deptno,
2 trim (both ',' from
3 replace(
4 replace(
5 max(decode(job,'ANALYST',job))||','||max(decode(job,'ANALYST',ename))||','||
6 max(decode(job,'CLERK',job))||','||max(decode(job,'CLERK',ename))||','||
7 max(decode(job,'MANAGER',job))||','||max(decode(job,'MANAGER',ename))||','||
8 max(decode(job,'PRESIDENT',job))||','||max(decode(job,'PRESIDENT',ename))||','||
9 max(decode(job,'SALESMAN',job))||','||max(decode(job,'SALESMAN',ename)),
10 ',,,',','),
11 ',,',',')
12 ) names
13 from emp
14 group by deptno
15 order by deptno
16 /
DEPTNO JOB/EMPLOYEE_NAME LIST
---------- --------------------------------------------------------------------------------
10 PRESIDENT,KING
20 ANALYST,SCOTT,CLERK,SMITH,MANAGER,JONES
30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,WARD

3 rows selected.


---------------------------------------------------------------
----another Example - 2

create table temp
(
source_1 varchar2(2),
period varchar2(4),
id_l number
)

insert into temp values ('A','2008',1);
insert into temp values ('A','2008',2);
insert into temp values ('A','2008',3);
insert into temp values ('A','2008',7);
insert into temp values ('B','2008',4);
insert into temp values ('B','2008',5);
insert into temp values ('B','2008',6);


select source_1,period,
rtrim(replace(replace(xmlagg(xmlelement("a",id_l)).getstringval(),'',NULL),'',','),',')
from temp
group by source_1,period


gives the output that you asked for..

A 2008 1,2,3,7
B 2008 4,5,6,8

======================

select deptno,ename,wm_concat(ename||' '||job) over (partition by deptno order by deptno) "ename/job"
from emp