Change separator of WM_CONCAT function of Oracle 11gR2

Question

Normally, WM_CONCAT is an aggregate function that return values from table separated by comma like here.

Suppose I have a table foo like this:

col_id     | col_text


111        | This

111        | is

111        | a

111        | test.

If I use this query:

SELECT CAST(WM_CONCAT(col_text) AS VARCHAR2(100)), col_id FROM foo

the result would be

This, is, a, test.

Is it possible to change the separator(',') to other characters like '.' or '|' of the WM_CONCAT() function?

Or create a user defined function that can be executed like WM_CONCAT()?

Answer 1

You might want to use LISTAGG.

SELECT col_id, 
       LISTAGG(col_text, '|') WITHIN GROUP (ORDER BY col_text) text
  FROM table1
 GROUP BY col_id

Output:

| COL_ID |            TEXT |
----------------------------
|    111 | This|a|is|test. |

SQLFiddle

UPDATE If you need to get distinct text values in a list

SELECT col_id, 
       LISTAGG(col_text, '|')
         WITHIN GROUP (ORDER BY col_text) text
  FROM 
(
  SELECT DISTINCT col_id, col_text
    FROM table1
)
 GROUP BY col_id

SQLFiddle

Answer 2

Problem with LISTAGG that it returns varchar2 and is limited to 4000 bytes

SELECT LISTAGG(LEVEL, CHR(10)) WITHIN GROUP (ORDER BY NULL)
  FROM Dual
CONNECT BY LEVEL < 2000

ORA-01489 Result of string concat is too large

I've found one workaround, but it looks ugly and is mutch slower

SELECT EXTRACT(XMLTYPE('<doc>' || XMLAGG(XMLTYPE('<ln>' || LEVEL || CHR(10) || '</ln>')).GetClobVal() || '</doc>'), '/doc/ln/text()').GetClobVal()
  FROM Dual
CONNECT BY LEVEL < 2000

Answer 3

Is it possible to change the separator(',') to other characters like '.' or '|' of the WM_CONCAT() function?

Do not use WM_CONCAT since it is an undocumented feature and it has been removed from the latest 12c version. Any application which has had been relying on wm_concat function will not work once upgraded to 12c. See Why not use WM_CONCAT function in Oracle?

SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT object_name
  2  FROM dba_objects
  3  WHERE owner='WMSYS'
  4  AND object_name LIKE 'WM\_%' ESCAPE '\';

OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES

20 rows selected.

You will receive an “invalid identifier” error:

SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
               *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

Therefore, there is no point relying on an undocumented feature which is no more made available in latest versions.

There are various string aggregation techniques:

• LISTAGG in 11gR2 and up

For example,

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

3 rows selected.

• ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in 9i and up

For example,

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;

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

3 rows selected.

• User-defined aggregate function STRAGG described in AskTom.
• COLLECT function in 10g and up

A few good examples by Tim Hall here.

Answer 4

The following worked for me on an ORACLE 10.2.0.5.0 database:

SELECT col_id, replace(wm_concat(col_text), ',', ' ') AS sentence
  FROM foo
 GROUP BY col_id;