Change Separator of Wm_Concat Function of Oracle 11Gr2

Change separator of WM_CONCAT function of Oracle 11gR2

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

Change WM_CONCAT function to LISTAGG

you should add Group by in your query

with tab as(
  select '10' as num, 'A' as product from dual union all
  select '10' as num, 'B' as product from dual union all
  select '10' as num, 'C' as product from dual union all
  select '10' as num, 'D' as product from dual union all
  select '20' as num, 'A' as product from dual union all
  select '20' as num, 'B' as product from dual union all
  select '30' as num, 'A' as product from dual
)

select  product
, count (1) how_many
, LISTAGG(num, ',') WITHIN GROUP (order by num) 
from tab
group by product

but you have to be careful, because by using listagg you can only concatenate strings up to 4K. There are also other string aggregation techniques, have a look at the answer here.

PRODUCT | HOW_MANY | LISTAGG(NUM,',')WITHINGROUP(ORDERBYNUM)
:------ | -------: | :--------------------------------------
A       |        3 | 10,20,30                               
B       |        2 | 10,20                                  
C       |        1 | 10                                     
D       |        1 | 10                                     

db<>fiddle here

Concatenate Over Oracle

OP is on Oracle 10g, and LISTAGG was introduced in 11g Release 2.

Therefore, in Oracle version prior to 11g where LISTAGG is not supported, you could use ROW_NUMBER() and SYS_CONNECT_BY_PATH functions.

SELECT fruit,
  LTRIM(MAX(SYS_CONNECT_BY_PATH(number,','))
  KEEP (DENSE_RANK LAST ORDER BY curr),',') AS fruits_agg
  FROM   (SELECT fruit,
                number,
                ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) AS curr,
                ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) -1 AS prev
         FROM   table_name)
  GROUP BY fruit
  CONNECT BY prev = PRIOR curr AND fruit = PRIOR fruit
 START WITH curr = 1;

NOTE

Never use WM_CONCAT since it is an undocumented feature and it has been removed from 12c version.

Any application which has had been relying on wm_concat function will not work once upgraded to 12c. Since, it has been removed. 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.

SQL>

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.

How can I combine multiple rows into a comma-delimited list in Oracle?

Here is a simple way without stragg or creating a function.

create table countries ( country_name varchar2 (100));

insert into countries values ('Albania');

insert into countries values ('Andorra');

insert into countries values ('Antigua');

SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

CSV                                                                             
--------------------------
Albania,Andorra,Antigua                                                         

1 row selected.

As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.

select listagg(country_name,', ') within group(order by country_name) csv
  from countries;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.

How can multiple rows be concatenated into one in Oracle without creating a stored procedure?

There are many way to do the string aggregation, but the easiest is a user defined function. Try this for a way that does not require a function. As a note, there is no simple way without the function.

This is the shortest route without a custom function: (it uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions )

SELECT questionid,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(elementid,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS elements
FROM   (SELECT questionid,
               elementid,
               ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) AS curr,
               ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) -1 AS prev
        FROM   emp)
GROUP BY questionid
CONNECT BY prev = PRIOR curr AND questionid = PRIOR questionid
START WITH curr = 1;

alternative to listagg in Oracle?

Use collect or write your own aggregation function.

---

Related Topics