String Aggregation in Oracle: Multiple Rows into Single Column

Posted: July 4, 2012 in Technical
Tags: , , ,

Requirement: We want to select a column from a table and its rows should be selected in a single line with comma separated.

SELECT ENAME FROM EMP;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN

Instead of above query output we want the output in below format

ENAME
---------------------------
SMITH,ALLEN,WARD,JONES,MARTIN

METHODS

1)User Defined String Aggregate function

One of the way to achieve the same is to create a user-defined function which will accept the query and will return the output in comma or any separator delimited format.

CREATE OR REPLACE FUNCTION STRING_AGGREGATE(i_query VARCHAR2,
i_seperator VARCHAR2 DEFAULT ',')
RETURN VARCHAR2
AS
l_return CLOB:='';
l_temp VARCHAR(32000);
TYPE r_cursor is REF CURSOR;
rc r_cursor;
BEGIN
OPEN rc FOR i_query;
LOOP
FETCH rc
INTO L_TEMP;
EXIT WHEN RC%NOTFOUND;
l_return:=l_return||L_TEMP||i_seperator;
END LOOP;
RETURN RTRIM(l_return,i_seperator);
END;
SELECT STRING_AGGREGATE('select ename from emp') ENAMES FROM DUAL;
ENAMES
---------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

SELECT STRING_AGGREGATE('select ename from emp','|') FROM DUAL
ENAMES
---------------------------
SMITH|ALLEN|WARD|JONES|MARTIN|BLAKE|CLARK|SCOTT|KING|TURNER|ADAMS|JAMES|FORD|MILLER

Problem with this method that, the function can return the value till VARCHAR2 Limit. To avoid this, change the return type to CLOB. Also if we want the string aggregated based on some grouping function then for that we have to twist the sql query little bit as follow

SELECT DEPTNO,STRING_AGGREGATE('SELECT ENAME FROM  EMP WHERE DEPTNO='||DEPTNO,'|')ENAME FROM EMP
GROUP BY DEPTNO
ORDER BY 1
DEPTNO  ENAME
---------------------
10	CLARK|KING|MILLER
20	SMITH|JONES|SCOTT|ADAMS|FORD
30	ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

2)WM_CONCAT

Second method of getting the desire result is using WM_CONCAT which is a unsupported and undocumented function. This function was introduced in Oracle 10g version.  It takes input as the column name and return the value with comma separated.

SELECT WM_CONCAT(ENAME) AS ENAMES FROM EMP;
ENAMES
---------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

Problem with this function are as follow
2.1) This is a unsupported function by oracle which means, that oracle does not recommend to use this function in PRODUCTION System
2.2) This function will always going to return the values in comma delimiter only. If you require in TAB or any other separator then you have to replace the comma with that separator.
2.3) This function is of return type VARCHAR2, which means that if the value is bigger then the VARCHAR2 then this function cannot be used.
2.4) This function does not support the order clause. To by pass this, write this function over a sub query which is having a order clause

SELECT WM_CONCAT(ENAME) AS ENAMES FROM  (SELECT ENAME FROM EMP ORDER BY ENAME);
ENAMES
---------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

3) STRAGG

This is another function which resides in SYS user. This function is also like WM_CONCAT, unsupported and undocumented.
Problem with this function are similar in nature, but the advantage of STRAGG over WM_CONCAT is that STRAGG is much faster then WM_CONCAT.

4) LISTAGG

LISTAGG is similar to WM_CONCAT and STRAGG, but the difference been that it is a documented and supported function by oracle. This function is a part of Oracle 11g Release 2 version.
Additional features of this function are
1) Can select your own separator. If we avoid the separator parameter, then it takes default value of NULL
2) Can get the ordered list by ordering the column
3) Supported by Oracle, can be used in Production system
4) Much faster then WM_CONCAT and little better than STRAGG function

SQL>SELECT LISTAGG(ENAME,',') WITHIN GROUP( ORDER BY ENAME) A FROM EMP;
A
---------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

SQL>SELECT LISTAGG(ENAME,',') WITHIN GROUP( ORDER BY DEPTNO) A FROM EMP;
A
---------------------
CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>SELECT LISTAGG(ENAME,'|') WITHIN GROUP( ORDER BY DEPTNO) A FROM EMP;
A
---------------------
CLARK|KING|MILLER|ADAMS|FORD|JONES|SCOTT|SMITH|ALLEN|BLAKE|JAMES|MARTIN|TURNER|WARD

Query 1:In first query we are passing the separator as comma(,) and getting the list which is order by ENAME
Query 2:In first query we are passing the separator as comma(,) and getting the list which is order by DEPTNO
Query 3:In first query we are passing the separator as PIPE(|) and getting the list which is order by DEPTNO

But the basic problem with this function is that the return type of this is VARCHAR2, which limits the number of records this can aggregate together.

Apart from the above methods few more methods for aggregating the data are given below
1) COLLECT Function (Oracle 10g)
2) Model Function
3)XMLAGG Function

Source: Oracle Documents

About these ads

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