得到如下结果的方式：
DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
--1.第一种方式（采用wm_concat函数）>=10g
SELECT deptno, wm_concat(ename) AS employees FROM emp GROUP BY deptno;
--2.第二种方式（采用listagg）11.2版本采用该函数
SELECT deptno,
listagg(ename, ',') within GROUP(ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
--3.第三种方式（用户定义的聚集函数）
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
g_string VARCHAR2(32767),
STATIC FUNCTION odciaggregateinitialize(sctx IN OUT t_string_agg)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(SELF IN OUT t_string_agg,
VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(SELF IN OUT t_string_agg,
ctx2 IN t_string_agg) RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(SELF IN t_string_agg,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY t_string_agg IS
STATIC FUNCTION odciaggregateinitialize(sctx IN OUT t_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate(SELF IN OUT t_string_agg,
VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
self.g_string := self.g_string || ',' || VALUE;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate(SELF IN t_string_agg,
returnvalue OUT VARCHAR2,
flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnvalue := rtrim(ltrim(self.g_string, ','), ',');
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge(SELF IN OUT t_string_agg,
ctx2 IN t_string_agg) RETURN NUMBER IS
BEGIN
self.g_string := self.g_string || ',' || ctx2.g_string;
RETURN odciconst.success;
END;
END;
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
SELECT deptno, string_agg(ename) AS employees FROM emp GROUP BY deptno;
--4.第四种方式（具体的函数）
CREATE OR REPLACE FUNCTION get_employees(p_deptno IN emp.deptno%TYPE)
RETURN VARCHAR2 IS
l_text VARCHAR2(32767) := NULL;
BEGIN
FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP
l_text := l_text || ',' || cur_rec.ename;
END LOOP;
RETURN ltrim(l_text, ',');
END;
SELECT deptno, get_employees(deptno) AS employees FROM emp GROUP BY deptno;
--减少函数调用次数，先将deptno不同的取出来就可以
SELECT e.deptno, get_employees(e.deptno) AS employees
FROM (SELECT DISTINCT deptno FROM emp) e;
--5.第五种方式（采用游标）
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)) employees
FROM emp e1
GROUP BY e1.deptno;
--减少函数调用次数
SELECT deptno,
concatenate_list(CURSOR (SELECT e2.ename
FROM emp e2
WHERE e2.deptno = e1.deptno)) employees
FROM (SELECT DISTINCT deptno FROM emp) e1;
--6.第六种方式（ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i）
SELECT deptno,
ltrim(MAX(sys_connect_by_path(ename, ','))
keep(dense_rank LAST ORDER BY curr),
',') AS employees
FROM (SELECT deptno,
ename,
row_number() over(PARTITION BY deptno ORDER BY ename) AS curr,
row_number() over(PARTITION BY deptno ORDER BY ename) - 1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr
AND deptno = PRIOR deptno
START WITH curr = 1;
--7.第七种方式（COLLECT function in Oracle 10g）
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION tab_to_string(p_varchar2_tab IN t_varchar2_tab,
p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
l_string VARCHAR2(32767);
BEGIN
FOR i IN p_varchar2_tab.first .. p_varchar2_tab.last LOOP
IF i != p_varchar2_tab.first THEN
l_string := l_string || p_delimiter;
END IF;
l_string := l_string || p_varchar2_tab(i);
END LOOP;
RETURN l_string;
END tab_to_string;
SELECT deptno,
tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM emp
GROUP BY deptno;
