On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. EMP table as an example, we might want to retrieve a list of employees for each department. Below is a list of the base data and the type of output we would like to return from an aggregate query. This article is based on a thread from asktom.
The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.
If this concerns you, use a User-Defined Aggregate Function described below. Thanks to Kim Berg Hansen for some corrections in comments. One approach is to write a specific function to solve the problems. To reduce the number of calls to the function, and thereby improve performance, we might want to filter the rows in advance. An alternative approach is to write a function to concatenate values passed using a ref cursor.
This is essentially the same as the previous example, except that the cursor is passed in making it generic, as shown below. Once again, the total number of function calls can be reduced by filtering the distinct values, rather than calling the function for each row.
An example on williamrobertson. An example on oracle-developer. This method requires a table type and a function to convert the contents of the table type to a string. I've altered his method slightly to bring it in line with this article.
For example, consider the following resultset:. We can see that the employee names have simply been grouped and concatenated into a single column values are delimited by comma per group.
IBM Community Home
As stated, there are several techniques available to perform this aggregation references are provided at the end of this articlebut the new LISTAGG function makes it much easier, as we will see below. The following elements are mandatory:.
We will begin with a simple example that aggregates the employee names for each department in the EMP table, using a comma as delimiter. Note that we chose to order the employees within each aggregation by the employee name. It should be noted that ordering the elements of a string aggregation is not a trivial task in some of the alternative techniques to LISTAGG.
In the following example, we will aggregate the employee names but order them by their respective hire dates. The following example demonstrates the analytic equivalent of our previous example for each department, aggregate employee names in hire date order. We can clearly see this above. The string aggregation for each department is available on every corresponding row. If the order of the aggregated elements is irrelevant to us, we can use a constant expression such as NULL, as follows.
In this example, the elements have been aggregated alphabetically, despite the NULL ordering clause. We can use a range of constants or expressions as a delimiter for our aggregated strings.
In fact, the delimiter is optional and can be excluded altogether, as the following example demonstrates. One restriction is that delimiters have to be either constants such as a literal or based on a deterministic expression that includes a column or expression used in the grouping. If we try to use our grouping column, we are limited to the type of expression we can use, as we can see below.
This time, Oracle recognises that we tried to use our grouping column, but we did not use a valid expression hence we receive another error message; albeit different to the previous example. The following is an example of a deterministic expression on our grouping column that Oracle will accept. This is a deterministic expression on the value of the grouping column and Oracle allows it.
The methods we will compare are as follows:. The main difference with LISTAGG is that it is a built-in function, so we should expect its performance to be at the very least comparable to its alternatives.
For the performance comparisons, we will use a larger source dataset of 1 million rows spread evenly between groups, as follows. Note that the sample data is cached prior to the comparisons.
We will begin by preparing our environment as follows. We will aggregate and order the value string within each of our groups, as follows. Our final performance comparison is with an example of string aggregation that uses the MODEL clause. The following example was sourced from Rob van Wijk's About Oracle blog and has been modified to fit our sample data. This ran for over 3 minutes! We can see from the statistics that it has incurred a significant number of physical reads, recursive calls and in-memory sorting.
An alternative example from Gary Myers' Sydney Oracle Lab blog displays similar performance characteristics. To summarise, therefore, the new LISTAGG function is the fastest technique for string aggregation and has the additional benefit of being a simple built-in function. Note that there is an issue with the MODEL examples used in the performance comparisons, but this might be isolated to either the version of Oracle used for the examples For a good summary of common string-aggregation techniques, see this article by Tim Hall and this article by William Robertson.
The dark mode beta is finally here. Change your preferences any time. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.
SQL | LISTAGG
I tried to find some function like opposite of listagg but couldn't find any. Thanks in advance As be here now has already noted in the comment Oracle doesn't provide such a function.
So as a quick workaround you could write similar query:. There is listunagg function provided by OraOpenSource Utils package set. It also works well. Learn more.
Ask Question. Asked 7 years, 5 months ago. Active 1 year ago. Viewed 12k times. APC k 18 18 gold badges silver badges bronze badges. Jaanna Jaanna 1, 9 9 gold badges 19 19 silver badges 43 43 bronze badges. Active Oldest Votes. Nick Krasnov Nick Krasnov ZZa ZZa 1 1 gold badge 11 11 silver badges 22 22 bronze badges. Fred N.
Vogt Fred N. Vogt 1 1 1 bronze badge. So can't be longer than 30 chars, can't contain most special characters, can't start with a number, etc. That last point rules it out for this question. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.
Post as a guest Name. Email Required, but never shown. The Overflow Blog. Featured on Meta.Register and Participate in Oracle's online communities. Learn from thousand of experts, get answers to your questions and share knowledge with peers.
Using LISTAGG to Turn Rows of Data into a Comma Separated List
Listagg or xmlagg? Which one is better in terms of Performance? Which one results into better cost optimization? You cannot directly compare costs of explain plans between different queries.
Well, there is one more difference between the two. Not sure what you mean Solomon. So it is some small but still overhead. Anybody can do that. The point is that most of what you posted is relating to old versions of the database, so whilst your giving the impression that there are Bugs and Performance issues with using XMLAGG, many of those things are resolved or improved in recent versions of the database, and one of the issues you link to was more to do with the way the OP was treating their XML, rather than actually being a problem with XMLAGG itself.
Over the last day or two we helped a poster on another thread deal with a bad table design, where department numbers were held in colon-separated strings, like ''.
It doesn't matter what the assignment was; could as well have been to normalize the data, then one would have to take the legacy tables and create the new ones using some sort of string splitting. It is easy with LISTAGG, but then I wanted to compare the performance of different approaches and for that I needed to create a realistic input, like you may have in a large corporation. There may easily be hundreds of departments my wife's company has over 6, and all being digits, plus the colon for each, you quickly exceed the max length for VARCHAR2.
Please type your message and try again. This discussion is archived. I have the same question Show 0 Likes 0. This content has been marked as final. Show 12 replies. Listagg for strings, xmlagg for xmlelements. They serve a different purpose, why would you want to compare their performance? Optimizer COST does not apply to functions. And don't go worrying about "cost optimization".
Please try and only link to things that are actually relevant and current. Thanks Blu for your valuable feedback. And another thing he is also not given the database version in the post. Go to original post.The tutorial assumes you know some basics of SQL, using aggregate functions and analytic functions. Sounds Greek and Latin? How can you get the result grouped by grades?
The output is presented side by side for a comparison. This is for illustrative purposes only. It is just the opposite in the second row. However, it is a bad idea to use a column value as a delimiter unless you have an absolute requirement for this. Hope the examples we have provided helped you to understand LISTAGG functionality, and you are now geared to use this function in your day today requirements powerfully. Thanks for the walk-through.
I have recently come across and love this function. The different steps involved in this example are summarized below:. This is shown below:. To explain this, we will insert a row into the table with a NULL value on last name as shown below:.
So, be ready to modify your test table a bit now and use the SQLs below for that:. Share this Post. Posted by YellowRose. MFroese February 5, at PM. Newer Post Older Post Home.
Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. Based on that familiarity, it builds the concept of analytic functions through a series of examples. It is true that whatever an analytic function does can be done by native SQL, with join and sub-queries. But the same routine done by analytic function is always faster, or at least as fast, when compared to native SQL.
Moreover, I am not considering here the amount of time that is spent in coding the native SQLs, testing, debugging and tuning them. The general syntax of analytic function is: Function arg1, How are analytic functions different from group or aggregate functions?
Query-1 returns departments and their employee count. As such any non-"group by" column is not allowed in the select clause. Now consider the analytic function query Query-2 and its result. This brings out the main difference between aggregate and analytic functions.
Though analytic functions give aggregate result they do not group the result set. They return the group value multiple times with each record. As such any other non-"group by" column or expression can be present in the select clause, for example, the column EMPNO in Query Note the results of Query-3 and compare it with the result of aggregate function query Query In the next example we will see how to specify that.
How to specify the order of the records in the partition? That is the reason of clubbing them together. It is very useful in reporting, especially in places where different partitions have their own serial numbers. Suppose we rank a group of record and found several records in the first rank. Now we want to apply an aggregate function on the records of the first rank.
The LAST function is used in similar context to perform computations on last ranked records. Query 9 -- How each employee's salary compare with the average salary of the first -- year hires of their department?
An important feature of the windowing clause is that it is dynamic in nature. For ROW type windows the definition is in terms of row numbers before or after the current row. We will take this up in details latter.Single-row functions return a single result row for every row of a queried table or view. Numeric functions accept numeric input and return numeric values. The numeric functions are:. Character functions that return character values return values of the following data types unless otherwise documented:.
The length of the value returned by the function is limited by the maximum length of the data type returned. For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data. Character functions that return number values can take as their argument any character data type. The character functions that return number values are:. The character set functions return information about the character set.
The character set functions are:. The remaining datetime functions were designed to accept any of the three types of data date, timestamp, and interval and to return a value of one of these types. The general comparison functions determine the greatest and or least value from a set of values. The general comparison functions are:.
Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention datatype TO datatype. The first data type is the input data type. The second data type is the output data type. The SQL conversion functions are:. Hierarchical functions applies hierarchical path information to a result set. The hierarchical function is:.