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

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

顯示相關的資料在一行，找到一個站點，總結了N多方法：



我第一個知道的是：
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 ;