做网站 百度推广/东莞企业网站排名优化

            现在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,MILLERSQL> 
SQL> 
SQL> SELECT  T.DEPTNO,listagg(T.ENAME,',')  within group (order by T.EMPNO) CONCAT FROM EMP T2  GROUP BY T.DEPTNO;DEPTNO CONCAT
---------- --------------------------------------------------10 CLARK,KING,MILLER20 SMITH,JONES,SCOTT,ADAMS,FORD30 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  CONCAT3  FROM EMP T;DEPTNO ENAME      CONCAT
---------- ---------- --------------------------------------------------10 CLARK      CLARK,KING,MILLER10 KING       CLARK,KING,MILLER10 MILLER     CLARK,KING,MILLER20 SMITH      SMITH,JONES,SCOTT,ADAMS,FORD20 JONES      SMITH,JONES,SCOTT,ADAMS,FORD20 SCOTT      SMITH,JONES,SCOTT,ADAMS,FORD20 ADAMS      SMITH,JONES,SCOTT,ADAMS,FORD20 FORD       SMITH,JONES,SCOTT,ADAMS,FORD30 ALLEN      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES30 WARD       ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES30 MARTIN     ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMESDEPTNO ENAME      CONCAT
---------- ---------- --------------------------------------------------30 BLAKE      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES30 TURNER     ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES30 JAMES      ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES14 rows selected.SQL>