从上面的地址中取的:有表: SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;DEPTNO ENAME ------ ---------- 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES 20 SCOTT 20 SMITH 30 ALLEN 30 BLAKE 30 JAMES 30 MARTIN 30 TURNER 30 WARD14 rows selected. 想输出为: DEPTNO ENAME ------ ---------- 10 CLARK, KING, MILLER 20 ADAMS, FORD, JONES, SCOTT, SMITH 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD============================================================================SELECT deptno, LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated 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;
SYS_CONNECT_BY_PATH 同上 來遲了,呵呵
DECLARE RESULT TABLE1.NAME%TYPE; CURSOR TEMP IS SELECT NAME FROM TABLE1; BEGIN RESULT := ''; FOR ROW IN TEMP RESULT := RESULT || ROW.NAME || ','; END LOOP; END; RESULT就是想要的结果了!
同一楼上的 DECLARE RESULT TABLE1.NAME%TYPE; CURSOR TEMP IS SELECT NAME FROM TABLE1; BEGIN RESULT := ''; FOR ROW IN TEMP RESULT := RESULT || ROW.NAME || ','; END LOOP; END; RESULT就是想要的结果了!
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD14 rows selected.
想输出为:
DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD============================================================================SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
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;
同上
來遲了,呵呵
RESULT TABLE1.NAME%TYPE;
CURSOR TEMP IS
SELECT NAME FROM TABLE1;
BEGIN
RESULT := '';
FOR ROW IN TEMP
RESULT := RESULT || ROW.NAME || ',';
END LOOP;
END;
RESULT就是想要的结果了!
DECLARE
RESULT TABLE1.NAME%TYPE;
CURSOR TEMP IS
SELECT NAME FROM TABLE1;
BEGIN
RESULT := '';
FOR ROW IN TEMP
RESULT := RESULT || ROW.NAME || ',';
END LOOP;
END;
RESULT就是想要的结果了!