Updated February 27, 2023
What is an Oracle LISTAGG() Function?
An Oracle LISTAGG Function is an aggregate function that returns a single row. This is used to transform data from multiple rows into a single list of values separated by a given delimiter. It operates on all rows and returns single.
- It returns a comma or other delimiter separatedresult set just like an excel CSV file.
- It returns a string value.
- As it is an aggregation function, if any non-aggregate column is being used in SELECT statement then that non-aggregate column must be declared in the GROUP BY Clause as well.
- It is an analytic function.
- This function partition the result set into groups with the use of OVER ( ) clause.
- The return result set size of this function is 4000 bytes.
Syntax:
LISTAGG(Column [, Delimiter])WITHIN GROUP (Order by Col_name) [Over(Partition by Col_name)]
Description:
- COLUMN: It’s a column name in which LISTAGG function operates. It can be a column, constant, or expression.
- Delimiter: It is used to separate the values in the result row. It is a string type. It is optional and by default it is NULL.
- WITHIN GROUP: It is a mandatory clause.
- Order by Col_name: Order by clause is used for sorting the data according to the given Col_name. Col_name can be a column name or expression for sorting the data. By default, the order is ascending.
- Over(Partition by Col_name): This is an optional clause. It is used with the LISTAGG function for grouping the result set based onCol_name. Col_name can be a column name or expression(s) in the Partition by clause. This clause is used as an analytic function.
How Does LISTAGG() Function Works in Oracle?
As the LISTAGG function name suggests it’s an aggregate function that returns single-row result set. It operates on all rows of a column and transforms them into a single result set row. The result values can be separated using a delimiter.
Examples to Implement of LISTAGG() Function
In this section, we’ll see the implementation of Oracle LISTAGG Function and its behavior. For that, we will use the below sample table (Employee) with 14 records to understand the Oracle LISTAGG() function behavior.
Example #1 – Group List
Code:
SELECT * Employee;
Output:
Example #2 – LISTAGG() Function
Code:
SELECT LISTAGG(Name,'\') WITHIN GROUP (ORDER BY Name)Agg_Name FROM Employee;
Output:
In the above example, LISTAGG function aggregating name column data and returning a single-row result set.
Example #3 – LISTAGG() Function without GROUP BY Clause
As the previous example returns a single row result set without GROUP BY Clause.
Code:
SELECT Deptnumber, LISTAGG(Name,'\') WITHIN GROUP (ORDER BY Name)Agg_Name FROM Employee;
Output:
The above SQL statement throws an error, WHY?
As LISTAGG function is an Aggregate function that combines the rows and returns a single row result set but in the above example, there is one column Deptnumber which is a non-aggregate column. And no GROUP BY clause is not being used for grouping the data.
To prevent that error GROUP BY clause can be a better option.
Code:
SELECT Deptnumber, LISTAGG(Name,'\') WITHIN GROUP (ORDER BY Name)Agg_Name FROM Employee GROUP BY Deptnumber;
Output:
GROUP BY clause is used for grouping the data. If any aggregate function is being used in the SELECT statement with any non-aggregate column then GROUP BY clause must be used for the non-aggregate column to group the data accordingly.
Example #4 – LISTAGG() Function without WITHIN GROUP Clause
SELECT LISTAGG(Name,'\') Agg_Name FROM Employee;
Output:
It’s throwing an error because the WITHIN GROUP clause is missing. It’s a mandatory clause to be used with the LISTAGG function.
Example #5 – LISTAGG() Function with [Over (Partition by Col_name)] Clause
Code:
SELECT Deptnumber, Designation, LISTAGG(Name,'|') WITHIN GROUP (ORDER BY Designation, Name) OVER(Partition by Deptnumber) Agg_Name FROM Employee ORDER BY Deptnumber, Designation, Name;
Output:
The above analytic example shows, for each employee hired in the department, and all other employees in that department.
Important Tips
1. For delimiter, other symbols also can be used.
2. LISTAGG function returns an error if the result row exceeds the maximum size i.e. 4000 bytes.
3. In Oracle 12C release 2, Oracle introduced two clauses to control the error or issue.
- ON OVERFLOW ERROR
It tells Oracle to return an error if the size exceeds.
Code:
LISTAGG(Column [, Delimiter] ON OVERFLOW ERROR) WITHIN GROUP(Order by Col_name)
Error code: ORA-01489
- ON OVERFLOW TRUNCATE
It handles the sie error very well. By default truncation literal is ‘…’.
Code:
LISTAGG(Column [, Delimiter] ON OVERFLOW TRUNCATE) WITHIN GROUP (Order by Col_name)
Conclusion
Oracle LISTAGG() function is a very useful function for concatenating or transforming multiple rows into a single row result set. It is an Aggregate as well as analytical function. This function is used to de-normalize many rows value into a single row value using a delimiter.
Recommended Articles
This is a guide to Oracle LISTAGG(). Here we discuss the What is an Oracle LISTAGG() Function and its syntax. We also discussed how Does LISTAGG() Function Works in Oracle along with examples. You can also go through our suggested articles to learn more –
