Oracle-11g 新函数 LISTAGG 行转列

最新推荐文章于 2024-03-15 16:17:03 发布

suyishuai

最新推荐文章于 2024-03-15 16:17:03 发布

阅读量2.1w

点赞数 1

分类专栏： oracle 函数杂谈文章标签： oracle 11g 行转列 LISTAGG

本文链接：https://blog.csdn.net/suyishuai/article/details/25879741

版权

oracle 函数杂谈专栏收录该内容

7 篇文章 0 订阅

订阅专栏

现在oracle数据库都在用11g的了，oracle也推出了行转列的函数LISTAGG ，我们用惯了10g的内部函数wmsys.wm_concat行转列了，出于安全考虑，大家还是用新的LISTAGG 函数吧。先说下这个函数的用法。

帮助文档位置在SQL Language Reference->Functions->LISTAGG

函数签名中的measure_expr为分组中每个列的表达式，而delimiter为合并分割符。如果delimiter不设置的话，就表示无分割符。中间within group后面的order_by_clause表示的是进行合并中要遵守的排序顺序。而后面的over子句表明listagg是具有分析函数analyze funcation特性的。

下面就演示几个常用的例子。

SQL> 
SQL> SELECT  listagg(T.ENAME,',')  within group (order by T.EMPNO) CONCAT FROM EMP T;

CONCAT
--------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KI
NG,TURNER,ADAMS,JAMES,FORD,MILLER


SQL> 
SQL> 
SQL> SELECT  T.DEPTNO,listagg(T.ENAME,',')  within group (order by T.EMPNO) CONCAT FROM EMP T
  2  GROUP BY T.DEPTNO;

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

SQL> 
SQL> 
SQL> SELECT T.DEPTNO,T.ENAME,
  2    listagg(T.ENAME, ',') within group(order by T.EMPNO) over(partition by T.DEPTNO) as  CONCAT
  3  FROM EMP T;

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

    DEPTNO ENAME      CONCAT
---------- ---------- --------------------------------------------------
        30 BLAKE      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        30 TURNER     ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        30 JAMES      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

14 rows selected.

SQL>