You are reading the article How Does Listagg() Function Works In Oracle? updated in October 2023 on the website Cersearch.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How Does Listagg() Function Works In Oracle?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.
Start Your Free Data Science Course
Hadoop, Data Science, Statistics & others
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.
LISTAGG(Column [, Delimiter])WITHIN GROUP (Order by Col_name) [Over(Partition by Col_name)]
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? 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
SELECT * Employee;
Output:Example #2 – LISTAGG() Function
SELECT LISTAGG(Name,'') WITHIN GROUP (ORDER BY Name)Agg_Name FROM Employee;
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.
SELECT Deptnumber, LISTAGG(Name,'') WITHIN GROUP (ORDER BY Name)Agg_Name FROM Employee;
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.
SELECT Deptnumber, LISTAGG(Name,'') WITHIN GROUP (ORDER BY Name)Agg_Name FROM Employee GROUP BY Deptnumber;
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;
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
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.
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 ‘…’.
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 –
You're reading How Does Listagg() Function Works In Oracle?
Update the detailed information about How Does Listagg() Function Works In Oracle? on the Cersearch.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!