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 WARD两个字段,写SQL后取出的效果如下DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD请高手指点
------ ----------
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 WARD两个字段,写SQL后取出的效果如下DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD请高手指点
2 UNION
3 SELECT 10 DEPTNO,'KING' ENAME FROM DUAL
4 UNION
5 SELECT 10 DEPTNO,'MILLER' ENAME FROM DUAL
6 UNION
7 SELECT 20 DEPTNO,'ADAMS' ENAME FROM DUAL
8 UNION
9 SELECT 20 DEPTNO,'FORD' ENAME FROM DUAL
10 UNION
11 SELECT 20 DEPTNO,'JONES' ENAME FROM DUAL
12 UNION
13 SELECT 20 DEPTNO,'SCOTT' ENAME FROM DUAL
14 UNION
15 SELECT 20 DEPTNO,'SMITH' ENAME FROM DUAL
16 UNION
17 SELECT 30 DEPTNO,'ALLEN' ENAME FROM DUAL
18 UNION
19 SELECT 30 DEPTNO,'BLAKE' ENAME FROM DUAL
20 UNION
21 SELECT 30 DEPTNO,'JAMES' ENAME FROM DUAL
22 UNION
23 SELECT 30 DEPTNO,'MARTIN' ENAME FROM DUAL
24 UNION
25 SELECT 30 DEPTNO,'WARD' ENAME FROM DUAL
26 )
27 select DEPTNO,WMSYS.WM_CONCAT(ENAME) TIME FROM A
28 GROUP BY DEPTNO
29 /
DEPTNO TIME
---------- --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,SMITH,SCOTT,FORD,JONES
30 ALLEN,JAMES,WARD,MARTIN,BLAKE版本是10G及以上的,僅供參考啊!
SQL> WITH A AS (SELECT 10 DEPTNO,'CLARK' ENAME FROM DUAL
2 UNION
3 SELECT 10 DEPTNO,'KING' ENAME FROM DUAL
4 UNION
5 SELECT 10 DEPTNO,'MILLER' ENAME FROM DUAL
6 UNION
7 SELECT 20 DEPTNO,'ADAMS' ENAME FROM DUAL
8 UNION
9 SELECT 20 DEPTNO,'FORD' ENAME FROM DUAL
10 UNION
11 SELECT 20 DEPTNO,'JONES' ENAME FROM DUAL
12 UNION
13 SELECT 20 DEPTNO,'SCOTT' ENAME FROM DUAL
14 UNION
15 SELECT 20 DEPTNO,'SMITH' ENAME FROM DUAL
16 UNION
17 SELECT 30 DEPTNO,'ALLEN' ENAME FROM DUAL
18 UNION
19 SELECT 30 DEPTNO,'BLAKE' ENAME FROM DUAL
20 UNION
21 SELECT 30 DEPTNO,'JAMES' ENAME FROM DUAL
22 UNION
23 SELECT 30 DEPTNO,'MARTIN' ENAME FROM DUAL
24 UNION
25 SELECT 30 DEPTNO,'WARD' ENAME FROM DUAL
26 )
27 select DEPTNO,substr(MAX(SYS_CONNECT_BY_PATH(ENAME,',')),2) ENAME FROM
28 (SELECT DEPTNO,ENAME,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ) RN FROM A)
29 START WITH RN=1
30 CONNECT BY RN-1=PRIOR RN and deptno=prior deptno
31 GROUP BY DEPTNO
32 order by DEPTNO
33 /
DEPTNO ENAME
---------- --------------------------------------------------------------------------------
10 MILLER,KING,CLARK
20 JONES,FORD,ADAMS,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,WARD
不是的吧,假如我要是有几千条,那不写疯的了啊,能不能用一条SELECT语句直接完成啊,在网上找好像有个SYS_CONNECT_BY_PATH的,但自己怎么搞都搞不好,所以来问问高手们,谢谢
select DEPTNO,WMSYS.WM_CONCAT(ENAME) TIME FROM A GROUP BY DEPTNO3楼的是9的方法,也就是你说的SYS_CONNECT_BY_PATH
select DEPTNO,substr(MAX(SYS_CONNECT_BY_PATH(ENAME,',')),2) ENAME FROM
(SELECT DEPTNO,ENAME,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ) RN FROM A)
START WITH RN=1
CONNECT BY RN-1=PRIOR RN and deptno=prior deptno
GROUP BY DEPTNO
order by DEPTNO
是不是要一直写下去啊
谢谢 fxianxian
28 (SELECT DEPTNO,ENAME,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ) RN FROM A)
29 START WITH RN=1
30 CONNECT BY RN-1=PRIOR RN and deptno=prior deptno
31 GROUP BY DEPTNO
32 order by DEPTNO//报错:"字符串连接的结果过长"
28 29是行数,是不需要的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; 你把字段换下就可以了
的错
上面的9才可以用的