String Aggregation in Oracle: Multiple Rows into Single Column

Requirement:

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

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

METHODS

1)User Defined String Aggregate function

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

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

2)WM_CONCAT

Second method of getting the desire result is using Oracle provide string aggregation function 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.

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

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

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

Leave a Reply

Your email address will not be published.