Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换

最新推荐文章于 2024-09-24 10:50:41 发布

thomas0104

最新推荐文章于 2024-09-24 10:50:41 发布

阅读量96

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/thomas0104/article/details/84171603

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

有表：
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 WARD
14 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

10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD