玉面飞龙的BLOG

11gR2 - LISTAGG

April 5th, 2010 | Categories: Boring | Tags: Oracle

11gR2中提供了行列转换的函数–LISTAGG。可以实现如下功能。

SQL> SELECT deptno, LISTAGG(ename, ‘; ‘) WITHIN GROUP (order by ename) as
2 emp_list FROM emp GROUP BY deptno;

DEPTNO EMP_LIST
———- ———————————————————————-
10 CLARK; KING; MILLER
20 ADAMS; FORD; JONES; SCOTT; SMITH
30 ALLEN; BLAKE; JAMES; MARTIN; TURNER; WARD

这个功能在10g和9i中要通过自定义函数等方法实现。

For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the <measure> column.

As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.

As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.