Oracle: Difference between wm_concat and ListAgg

1. wm_concat is undocumented and unsupported by oracle, thus rendering production systems unsupported. listagg is documented and supported by oracle.

2. wm_concat allows distinct option. listagg does not allows it.
    select wm_concat(distinct ename) as enames from emp;

3. listagg allows to decide string concat order. wm_concat does not allows it.
    select listagg(ename,',') within group(order by empno desc) as enames from emp;

4. listagg allows to decide delimiter. wm_concat does not allows it.
    select listagg(ename,'***') within group(order by empno desc) as enames from emp;

5. wm_concat allows to be used olap function with order by listagg does not allows it. listagg allows only olap function without order by.
    select deptno,wm_concat(ename) over(order by deptno) as enames from emp;

6. wm_concat allows to be used keep. listagg does not allows it.
    select wm_concat(ename) keep(dense_rank first order by deptno) as enames from emp;

Original Post
http://forums.oracle.com/forums/thread.jspa?threadID=1088158

Related Links:

Oracle: New String Aggregation Techniques
http://nimishgarg.blogspot.com/2010/02/oracle-new-string-aggregation.html

Oracle: Difference between wm_concat and ListAgg
http://nimishgarg.blogspot.com/2010/07/oracle-differece-between-wmconcat-and.html

Oracle: Nth Record from Comma Separated String
http://nimishgarg.blogspot.com/2010/06/oracle-nth-record-from-comma-seprated.html

Oracle: Comma Separated String To Rows 11g
http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows.html

Oracle: Comma Separated String To Rows 10g Onwards
http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows_16.html

Oracle: Department wise Employee Ids Comma Separated (Analytical Functions )
http://nimishgarg.blogspot.com/2010/02/oracle-department-wise-employee-ids.html

Oracle: Comma Separated String To Rows
http://nimishgarg.blogspot.com/2009/12/oracle-sql-use-comma-seprated-string-to.html

6 comments:

Post a Comment