[20111220]listagg 11G的新特性.txt

[20111220]listagg 11G的新特性.txt

显示相关的数据在一行，找到一个站点，总结了N多方法：

www.oracle-base.com/articles/misc/StringAggregationTechniques.php

我第一个知道的是：
SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

后来了解了wmsys.wm_concat函数:
SELECT deptno,wmsys.wm_concat(ename) AS employees FROM emp GROUP BY deptno;

现在11G加入了listagg，感觉这个分析函数比wm_concat灵活一点，就是可以定义分割符号。

Select deptno,listagg(ename,'!') within group (order by sal) enames from emp group by deptno ;