来自:oracledbalgtu 给你个参考方法: SELECT DEPTNO, MAX(ONCATENATED) FROM (SELECT DEPTNO, LTRIM(SYS_CONNECT_BY_PATH(ENAME, ','), ',') ONCATENATED 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 SCOTT.EMP) START WITH CURR = 1 CONNECT BY PREV = PRIOR CURR AND DEPTNO = PRIOR DEPTNO) GROUP BY DEPTNO ORDER BY 1;
select wmsys.wm_concat(id),wmsys.wm_concat(name) from a
select wmsys.wm_concat(id)||' '||wmsys.wm_concat(name) from a
select wmsys.wm_concat(id),wmsys.wm_concat(name) from a 在10G以上版本
给你个参考方法:
SELECT DEPTNO, MAX(ONCATENATED)
FROM (SELECT DEPTNO,
LTRIM(SYS_CONNECT_BY_PATH(ENAME, ','), ',') ONCATENATED
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 SCOTT.EMP)
START WITH CURR = 1
CONNECT BY PREV = PRIOR CURR
AND DEPTNO = PRIOR DEPTNO)
GROUP BY DEPTNO
ORDER BY 1;
在10G以上版本