Oracle-11g 新函数 LISTAGG 行转列

来源：互联网发布：淘宝方正字体排查软件编辑：程序博客网时间：2024/05/16 10:54

现在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,KING,TURNER,ADAMS,JAMES,FORD,MILLERSQL> 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,JAMESSQL> 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,JAMES14 rows selected.SQL>

1 0