create table a100(deptno varchar2(10),ename varchar2(100));
SQL> set linesize 200
SQL> set pagesize 200
SQL> select * from a100;
DEPTNO ENAME
---------- ----------------------------------------------------------------------------------------------------
10 SMITH
10 JONES
10 SCOTT
20 ADAMS
20 FORD
20 JORDAN
6 rows selected.
SQL> SELECT
2 T .DEPTNO,
3 listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names
4 FROM
5 a100 t
6 group by deptno;
DEPTNO
----------
NAMES
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
JONES,SCOTT,SMITH
20
ADAMS,FORD,JORDAN
create table a200(deptno varchar2(10),ename varchar2(100),salary varchar2(100));
SQL> select * from a200 ;
DEPTNO ENAME SALARY
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
10 SMITH 100
10 JONES 200
10 SCOTT 50
20 ADMAS 34
20 FORD 89
20 JORDAN 99
6 rows selected.
SQL> SELECT
2 T .DEPTNO,
3 listagg (T .ENAME ||'&&'||t.salary,',') WITHIN GROUP (ORDER BY T .ENAME) names
4 FROM
5 a200 t
6 group by deptno;
DEPTNO
----------
NAMES
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
JONES&&200,SCOTT&&50,SMITH&&100
20
ADMAS&&34,FORD&&89,JORDAN&&99
SQL> SELECT
2 T .DEPTNO,
3 listagg (T .ENAME ||'&&'||t.salary,',') WITHIN GROUP (ORDER BY T .salary) names
4 FROM
5 a200 t
6 group by deptno;
DEPTNO
----------
NAMES
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
SMITH&&100,JONES&&200,SCOTT&&50
20
ADMAS&&34,FORD&&89,JORDAN&&99