有一张表结构如下:
id name type
1 li 1
2 liu 1
3 wang 2
4 ping 2
5 wen 2
想得到如下结果
id name type
1,2 li,liu 1
3,4,5 wang,ping,wen 2
这样的SQL语句该怎么写呢(用oracle9i),谢谢!
id name type
1 li 1
2 liu 1
3 wang 2
4 ping 2
5 wen 2
想得到如下结果
id name type
1,2 li,liu 1
3,4,5 wang,ping,wen 2
这样的SQL语句该怎么写呢(用oracle9i),谢谢!
有表:
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 除了使用聚集函数或者存储过程之外,9i中可以:
SQL> SELECT deptno
2 , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
3 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
4 FROM ( SELECT deptno
5 , ename
6 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
7 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
8 FROM emp )
9 GROUP BY deptno
10 CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
11 START WITH curr = 1;
DEPTNO----------CONCATENATED----------------------------------------------------------------------------------------------------
10CLARK,KING,MILLER
20ADAMS,FORD,JONES,SCOTT,SMITH
30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD10CLARK,KING,MILLER 20ADAMS,FORD,JONES,SCOTT,SMITH 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
id varchar2(20),
name varchar2(20),
type varchar2(20)
)
SELECT type
, LTRIM(MAX(SYS_CONNECT_BY_PATH(name,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS name
, LTRIM(MAX(SYS_CONNECT_BY_PATH(id,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS id
FROM ( SELECT type ,id
, name
, ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) AS curr
, ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) -1 AS prev
FROM test )
GROUP BY type
CONNECT BY prev = PRIOR curr AND type = PRIOR type
START WITH curr = 1;