Concatenate Over Oracle

Question

This is a sample table data

Fruit   Number
Apple    1
Apple    2
Apple    3
Kiwi     6
Kiwi     10

I try to concatenate the table column values to get the following

Fruit   Number
Apple    1-2-3
Kiwi     6-10

Is there a way to query this or store procedure? Something like Concatenate over(partition by) , I don't know much about stored procedures. Thanks!

Answer 1

You don't need a stored procedure for that. Use listagg function:

select fruit, listagg(number, ',') within group (order by number)
from mytable
group by fruit

If your database version is 10g it is still simple: use WM_CONCAT (if doesn't recognize the name probably try WMSYS.WM_CONCAT) function, see example in this answer. Just in case you need to use separator which is different from ,, you can wrap the result in replace function; if you want to order the result just pre-order it in a subquery, e.g.:

select fruit, replace(wm_concat(number), ',', '-')
from (select fruit, number
      from mytable
      order by number)
group by fruit

If you for some mysterious reason don't have the function in your instance you can use a polyfill, see my answer here.

Answer 2

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.