Connect By
The first example uses a recursive query which starts with the first node of every group and only shows those rows, where the whole string has been aggregated (connect_by_isleaf = 1).
SELECT deptno
, rn
, TRIM(leading ',' FROM SYS_CONNECT_BY_PATH(ename,','))
FROM (SELECT deptno
, ename
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) rn
FROM emp)
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY PRIOR deptno = deptno
AND PRIOR rn + 1 = rn
/
XMLAGG
another solution is to use xml functions like xmlagg (aggregate) and xmlelement.
SELECT deptno
,RTRIM(XMLAGG(XMLELEMENT(e,ename,',').EXTRACT('//text()')),',') name
FROM emp
GROUP BY deptno
/
WM_CONCAT
one more solution even if not documented…
SELECT deptno, WM_CONCAT(ename)
FROM emp
GROUP by deptno
/
and if you need the enames to be ordered…
SELECT deptno
, WM_CONCAT(ename)
FROM (SELECT deptno, ename
FROM emp
ORDER BY ename)
GROUP by deptno
/
User defined aggregator
Another solution is to write your own group function
CREATE OR REPLACE TYPE ListAggregator IS OBJECT (
vList VARCHAR2(4000)
,STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT ListAggregator) RETURN NUMBER
,MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT ListAggregator
, value IN VARCHAR2 ) RETURN NUMBER
,MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT ListAggregator
, returnValue OUT VARCHAR2
, flags IN NUMBER ) RETURN NUMBER
,MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
, ctx2 IN ListAggregator ) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY ListAggregator IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ListAggregator) RETURN NUMBER IS
BEGIN
sctx := ListAggregator(NULL);
RETURN ODCIConst.SUCCESS;
END ODCIAggregateInitialize;
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT ListAggregator
, value IN VARCHAR2 ) RETURN NUMBER IS
BEGIN
IF value IS NOT NULL
THEN
self.vList := concat(concat(self.vList,', '),value);
END IF;
RETURN ODCIConst.SUCCESS;
END ODCIAggregateIterate;
MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT ListAggregator
, returnValue OUT VARCHAR2
, flags IN NUMBER ) RETURN NUMBER IS
BEGIN
returnValue := LTrim(self.vList, ', ');
RETURN ODCIConst.SUCCESS;
END ODCIAggregateTerminate;
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT ListAggregator
, ctx2 IN ListAggregator) RETURN NUMBER IS
BEGIN
IF self.vList IS NULL
THEN
self.vList := ctx2.vList;
ELSIF ctx2.vList IS NOT NULL
THEN
self.vList := concat(concat(self.vList,', '),ctx2.vList);
END IF;
RETURN ODCIConst.SUCCESS;
END ODCIAggregateMerge;
END;
/
CREATE OR REPLACE FUNCTION LIST(vValue IN VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE
USING ListAggregator;
/
SELECT deptno
, LIST(ename)
FROM emp
GROUP BY deptno
/
LISTAGG
and finally if you are on ORACLE 11R2, there is a built-in function to do list aggregation
SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename)
FROM emp
GROUP BY deptno
/