Oracle: New String Aggregation Techniques

The LISTAGG analytic function was introduced in Oracle 11g Release 2

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

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




If you are not running 11g Release 2 (undocumented)

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

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




Check it out here: http://www.oracle-base.com:80/articles/misc/StringAggregationTechniques.php 


Related Links:
- LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: Difference between wm_concat and ListAgg
- Oracle: Comma Separated String To Rows 11g
- Oracle: Comma Separated String To Rows 10g Onwards
- Oracle: Nth Record from Comma Separated String
- Oracle: Department wise Employee Ids Comma Separated
- Oracle: Comma Separated String To Rows

8 comments:

  1. Why you haven't taken blog name as any keyword. with the help of keyword you can generate more traffic.

    ReplyDelete
  2. You can make you blog keyword specific so that chances of getting more traffic and returning also. visitor Nicely presented information in this post, I prefer to read this kind of stuff. The quality of content is fine and the conclusion is good. Thanks for the post.

    ReplyDelete
  3. Thanks a lot man..

    ReplyDelete