before and after listagg - cheeyoung/sqlplus-public GitHub Wiki

SQL> connect scott/tiger
SQL> ed select11.sql
set pagesize 40
set linesize 80

SELECT    d.dname
,    e.ename
FROM    dept d
INNER JOIN    emp e
USING (deptno)
/

SELECT    d.dname
,    LISTAGG(e.ename, ', ') WITHIN GROUP (ORDER BY e.ename)
FROM    dept d
INNER JOIN    emp e
USING (deptno)
GROUP BY    d.dname
/
:wq
SQL> @select11

DNAME          ENAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
RESEARCH       JONES
RESEARCH       FORD
RESEARCH       ADAMS
RESEARCH       SMITH
RESEARCH       SCOTT
SALES          WARD
SALES          TURNER
SALES          ALLEN
SALES          JAMES
SALES          BLAKE
SALES          MARTIN

14 rows selected.


DNAME
--------------
LISTAGG(E.ENAME,',')WITHINGROUP(ORDERBYE.ENAME)
--------------------------------------------------------------------------------
ACCOUNTING
CLARK, KING, MILLER

RESEARCH
ADAMS, FORD, JONES, SCOTT, SMITH

SALES
ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD


SQL> 

LISTAGG ( [ALL] [DISTINCT] <measure_column> [,<delimiter>] [ON OVERFLOW TRUNCATE [truncate_literal] | ON OVERFLOW ERROR  [WITH | WITHOUT COUNT]]) 
	WITHIN GROUP (ORDER BY <oby_expression_list>)