有时工作中会用到wm_concat列转行的函数,但是从10205之后,其返回结果为CLOB类型,在PL/SQL developer中显示为这样:



但是在sqlplus中显示正常:

SQL> SELECT wm_concat(column_name) FROM t1 GROUP BY table_name;

WM_CONCAT(COLUMN_NAME)
--------------------------------------------------------------------------------
ENAME,COMM,SAL,JOB
DEPTNO,LOC,DNAME
EMPNO,DEPTNO,COMM,SAL,HIREDATE,MGR,JOB,ENAME
GRADE,HISAL,LOSAL
1
2
3
4
5
6
7
8
此时可以使用dbms_lob函数:


另:
在11GR2中,使用listagg()函数代替wm_concat(),可以提高效率;而且listagg()还可以作为分析函数使用,添加over子句。

SELECT deptno,dbms_lob.substr(wm_concat(ename))
FROM scott.emp a
GROUP BY deptno;

SELECT deptno,listagg(ename,',') WITHIN GROUP (ORDER BY ename)
FROM scott.emp a
GROUP BY deptno;

SELECT DISTINCT deptno,listagg(ename,',') WITHIN GROUP (ORDER BY ename) OVER (PARTITION BY deptno)
FROM scott.emp a;

  

原文链接:https://blog.csdn.net/m0_37933891/java/article/details/71057020

posted on 2020-06-30 18:23  vinsonLu  阅读(1969)  评论(0编辑  收藏  举报