Home »

String Aggregation Function

Start here

This article lists functions that aggregate data from a number of row into a single row.

Base Data:

DEPTNO ENAME

———- ———-

20 SMITH

30 ALLEN

30 WARD

20 JONES

30 MARTIN

30 BLAKE

10 CLARK

20 SCOTT

10 KING

30 TURNER

20 ADAMS

30 JAMES

20 FORD

10 MILLER

Desired Output:

DEPTNO EMPLOYEES

———- ————————————————–

10 CLARK,KING,MILLER

20 SMITH,FORD,ADAMS,SCOTT,JONES

30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

1. LISTAGG Analystic Function in 11g Release 2

The LISTAGG analytic function was introduced in Oracle 11g  Release 2, making it very easy to aggregate strings. The nice thing  about this function is it also allows us to order the elements in the  concatenated list.

COLUMN employees FORMAT A50

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.

2. WM_CONCAT Built-in Function

If you are not running 11g Release 2, but are running a version of the  database where the WM_CONCAT function is present, then it is a zero  effort solution as it performs the aggregation for you.

COLUMN employees FORMAT A50

SELECT deptno, wm_concat(ename) AS employees

FROM   emp

GROUP BY deptno;

DEPTNO EMPLOYEES

———- ————————————————–

10 CLARK,KING,MILLER

20 SMITH,FORD,ADAMS,SCOTT,JONES

30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.

3. ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i

An example on williamrobertson.net uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions to achieve the same result without the use of PL/SQL or additional type definitions.

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.

4. COLLECT FUNCTION

SQL> SELECT deptno
  2  ,      COLLECT(ename) AS emps
  3  FROM   emp
  4  GROUP  BY
  5         deptno;

For more information

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: