现在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>