ITPub博客

首页 > Linux操作系统 > Linux操作系统 > LISTAGG() whith group()

LISTAGG() whith group()

原创 Linux操作系统 作者:Deep_Bule 时间:2011-12-30 08:47:34 0 删除 编辑
The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list.

Base Data:

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

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;
Desired Output: DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
You can also use function Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 WMSYS.WM_CONCAT()
for example:
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 select id,val from idtable;

ID VAL
---------- --------------------
10 abc
10 abc
10 def
10 def
20 ghi
20 jkl
20 mno
20 mno

8 rows selected

SQL> commit;

Commit complete

SQL>
SQL> SELECT ID, WMSYS.WM_CONCAT(VAL) AS ENAMES
2 FROM IDTABLE
3 GROUP BY ID;

ID ENAMES
---------- --------------------------------------------------------------------------------
10 abc,abc,def,def
20 ghi,jkl,mno,mno


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26118480/viewspace-714198/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: Oracle array usage
请登录后发表评论 登录
全部评论

注册时间:2011-08-04

  • 博文量
    22
  • 访问量
    79408