Aggregate text values

The Oracle 10+ way:

SELECT deptno, wm_concat(name) as employees
	FROM emp
	GROUP BY deptno;

The old way:

• source: http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  RETURN  VARCHAR2 IS
  l_return  VARCHAR2(32767);
  l_temp    VARCHAR2(32767);
BEGIN
  LOOP
	FETCH p_cursor
	INTO  l_temp;
	EXIT WHEN p_cursor%NOTFOUND;
	l_return := l_return || ',' || l_temp;
  END LOOP;
  RETURN LTRIM(l_return, ',');
END;
/

SELECT e1.deptno, concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) as employees
  FROM emp e1
  GROUP BY e1.deptno;