Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings
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 <strong>, listagg( ename, ',') within group (order by sal)</strong> 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 collect function in 10g – on Oracle-developer.net
- Tag Cloud analysis in SQL and PL/SQL – Using Collections, Table Operator, Multiset and Collect operator
- Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it
- SQL*Plus or Report style Break Groups in SQL Query
- Dynamic and Conditional grouping in SQL queries – for flexible results from single query (oh and a useful case for the CUBE operator)
- Yes, it is here too: the PIVOT operator in Oracle 11g
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- On the integrity of data in Java applications – presentation from JFall 2013
- Enriching XMLType data using relational data – XQuery and fn:collection in action
- Java 8 – Collection enhancements leveraging Lambda Expressions – or: How Java emulates SQL
- Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP
- Oracle Database 12c: Find most valuable player using MATCH_RECOGNIZE in SQL
- Oracle Database 12c: Pattern Matching through MATCH_RECOGNIZE in SQL
- Oracle Database 12c: joining and outer joining with collections
- Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack
- Oracle Database 12c: In Line PL/SQL Functions in SQL queries