In our series on the 11g R2 release of the Oracle RDBMS, AMIS staff discusses various functions, features an facilities that have been added, improved or extended in this latest release. These articles are the fruit of several months of studying this new release and projecting which new features and changes would be the most useful to us in our daily practice. This article introduced the LISTAGG operator, new in 11gR2.
An example usage of LISTAGG:
select deptno , avg(sal) avg_sal , listagg( ename, ',') within group (order by sal) enames from emp group by deptno
Oracle RDBMS 10g introduced the Collect operator. It could be used to aggregate values on all rows in a group into a collection – a predefined TABLE OF <some type>. This operator can be used in statements like this one:
create or replace type ename_type as table of varchar2(30) / select deptno , avg(sal) avg_sal , cast ( collect(ename) as ename_type ) enames from emp group by deptno /
There are some limitations on using this operator: it does not support an order by especially for the collected values, it requires a TYPE definition (usually TABLE OF VARCHAR2) that has to be created before the Collect can be used and it acts only as a pure Aggregate Function (typically in conjunction with a group by), it is not available as an Analytical function.
(edited) on second thoughts, I should have rephrased the above. First of all – as Laurent pointed out in his comment, an order by expression apparently is allowed with COLLECT (I never knew that one!) just as DISTINCT and UNIQUE can be used. I also found out that you can use COLLECT without predefining a collection type. If you execute a statement like collect(ename) as emps – without the cast to turn the collect outcome into a well defined collection, the database will generate a type for you, on the fly with a system generated name that is quite worthless, but it will work all the same.
The new LISTAGG is similar to yet different from the COLLECT: LISTAGG creates a delimiter (comma, period, colon, etc.) separated string of values. The LISTAGG takes an optional order by clause that specifies the sequence of the values in the string. It returns a VARCHAR2 – so no custom type needs to be set up. And: LISTAGG is available as Aggregate (with GROUP BY) and Analytical Function (with PARTITION clause).
A simple example of using LISTAGG:
select deptno , avg(sal) avg_sal , listagg( ename, ',') within group (order by sal) enames from emp group by deptno /
The results returned by this query:
Here we used LISTAGG as an aggregate function, together with the GROUP BY that in this case indicates that the concattenated string with ENAME values should create this string per department. Within the string, the enames should be ordered by the salary values of the employees.
The next example is on using the LISTAGG as an analytical function, producing aggregate values in line with the records rather than grouped by into a summary result. So for each record processed, the delimiter separated string is produced, in the same way other analytical functions function. That means for example that we could have a ‘running delimited string’ if we would add an order by to the over() clause without a windowing clause.
Here we are after the list of employees (colleagues) that work in the same department as the employee being processed:
select deptno , ename , sal , listagg( ename, ',') within group (order by ename) over (partition by deptno) colleagues from emp order by deptno , ename
The results:
Resources
11gR2 SQL Language Reference on Collect
11gR2 SQL Language Reference on LISTAGG
the collect function in 10g – on Oracle-developer.net
Hi Lucas,
That was good examples and feature. But how can you implement below same thing in 10g using collect or any other method
select deptno
, avg(sal) avg_sal
, listagg( ename, ‘,’) within group (order by sal)
enames
from emp
group
by deptno
/
The possibility to use the collect function in combination unique or distinct or order by is new Oracle 11gr1 . I believe it isn’t possible in Oracle 10.
I hoped that listagg would return a clob instead of a varchar2. What happens when the 4000 limit is surpassed? I can’t test it because I don’t have Linux so I can’t install 11gr2 yet.
No, I have no plan to go to OOW this year… did you submit some trivia questions to Lilian?
Kind regards 😉
Oops, in that case I have overlooked that particular feature in the COLLECT operator. And I stand corrected. That is the risk you run with such a sophisticated audience! Will we meet at OOW Laurent? Thanks for your comment! Best regards, Lucas
There are some limitations on using this [COLLECT] operator: it does not support an order by especially for the collected values
Really?
select collect(ename order by ename) from emp
seems to be a valid syntax