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:
- LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: New String Aggregation Techniques
- 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:

sap support costsJune 23, 2011 at 12:57 PM
Listagg is a new function that has been added in Oracle 11g for the sting aggregation. You have shared a whole list of differences between these two functions. Thanks for sharing this useful post.
Nimish GargJune 23, 2011 at 2:16 PM
This comment has been removed by the author.
AnonymousJune 28, 2012 at 12:17 PM
Its really a usefulinformation. Thanks. But cold you please tell us more on what does "unsupported by Oracle" means? That function is introduced by Oracle itself right ?
AnonymousApril 15, 2013 at 8:12 PM
Si necesitan esa función, encontré esto en una página y lo probé y funciona muy bien:

Luego cambién el wm_concat() de su select por stragg().

Saludos,

Nacho V.

15/04/2013 10:46:53
===================
* No existe la funcion wm_concat() en mi Oracle, la utilizaba antes del upgrade para poder concatenar los valores de los ...
Se reemplaza por estas funciones y tipos:

CREATE OR REPLACE TYPE string_agg_type AS OBJECT
(
total VARCHAR2(4000),

STATIC FUNCTION
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
RETURN NUMBER,

MEMBER FUNCTION
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN VARCHAR2 )
RETURN NUMBER,

MEMBER FUNCTION
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
RETURN NUMBER,

MEMBER FUNCTION
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
RETURN NUMBER
);
/

Type created.

SQL> CREATE OR REPLACE TYPE BODY string_agg_type
IS

STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
RETURN NUMBER
IS
BEGIN
sctx := string_agg_type( null );
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
RETURN NUMBER
IS
BEGIN
self.total := self.total || ',' || value;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
RETURN NUMBER
IS
BEGIN
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
RETURN NUMBER
IS
BEGIN
self.total := self.total || ctx2.total;
return ODCIConst.Success;
END;

end;
/

Type body created.

SQL> CREATE or replace FUNCTION stragg(input VARCHAR2)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

Function created.
ConnectorsNovember 23, 2023 at 9:11 PM
WM_CONCAT is a function primarily used in Oracle databases for string aggregation. It concatenates values from multiple rows into a single string, separated by a specified delimiter. However, it's essential to note that WM_CONCAT is specific to Oracle and not part of the standard SQL.
pg slotFebruary 3, 2024 at 3:25 PM
โปรโมชั่น pg slot มากมาย เล่นง่ายจ่ายจริง แตกจริง ต้อง pg slot เท่านั้น! เล่นสล็อต พีจีสล็อต เว็บไซต์ตรงผู้ให้บริการเกมสล็อตออนไลน์ชั้นหนึ่ง ทกลอง เล่น ฟรี พร้อมโบนัส