sql - LISTAGG in oracle to return different values
I'm trying to use the LISTAGG function.I want to get the distinct values for that column.Is there a way to get the distinct values without creating a function or procedure?
The LISTAGG of col2 is not considered.This is the result of LISTAGG: [2,2,3,4,5], when I do that.
You may have to trim trailing and leading space depending on your data, but the above will work in most cases.
The result of string concatenation is too long if you have a lot of items in a group.
You can suppress this error from oracle 12cR2.Put a max number on each group's members.If it's ok to list only the first members, this will work.This may not work if you have long strings.You will have to experiment.
It is not easy to avoid the oracle string size limit.Thanks to user 3465996 for this post.
If you want to convert clob to varchar2, you can use this function.
If you want distinct values across MULTIPLE columns, want control over sort order, don't want to use an undocumented function that may disappear, and do not want more than one full table Scan, you may find this construct useful.
I grouped the values first, then did another aggregation with the listagg.Something like this.
I have extended a horse with no name's solution if the intent is to apply this transformation to multiple columns.
This is the release of the oracle database 11g enterprise edition.I couldn't use STRAGG because there was no way to order it.Since I am adding all columns of interest, performance scales linearly.3 seconds was taken for 77K rows.For a single rollup,.172 seconds.There is a way to separate multiple columns in a table.
If you want to get around the string length issue, you can use XMLAGG which is similar to listagg but returns a clob.
If you want to get the unique values, you can use regexp_replace and then turn it into a string.You will run out of space if you have a lot of distinct values, but the code below should work in most cases.
You can change the words you use.I wanted '-' instead of ',' but you should be able to replace the dashes in my code if you want that.
There is further refining going on using DECODE vs CASE.The case approach answer is also provided by Martin Vrbovsky.
The LISTAGG aggregate function now supports duplicate elimination.The LISTAGG aggregate function orders the rows for each group in a query according to the ORDER BY expression and then concatenates the values into a single string.Before concatenation into a single string, duplicate values can be removed from the specified expression.Before using the aggregate LISTAGG function, the need to create complex query processing is eliminated.The LISTAGG function can be used to process the removal of duplicate values.The result is more efficient.
If the previous record had the same value as the new one, you could use the lag function to analyse it.
Is there a way to use a PARTITION BY clause?It worked for me to get a list of application services.
If the columns value is null, then it's not appended to the LISTAGG string.
I used regular expressions in my function to handle this.The listagg call itself is one of the parameters.
If you don't need a particular order of values, you can use the separator.
The problem with LISTAGG is that if the total length of the string exceeds the limit in the database, the below error is thrown.
There is a new feature added in 12cR2.This clause would be included in the query.
Pick col1, listaggr(col2,',') within group from table group by col1 meaning aggregate the strings into list keeping the order n then afterwards deal with the duplicate in 1 group.If you want col3 as well, you need to add one more listagg that is select col1, listaggr(col2,') within the group.
The best way for simple queries is to use SELECT DISTINCT before calling LISTAGG.
It might not be possible in more complex queries.This came up in a scenario where the top-n approach was used.
The aggregate function was found by me.The UNIQUE or DISTINCT modifier is documented.It fails quietly in 10g.From another answer, I came to this solution.
In the case of a VARCHAR, you would need to define the tab_typ as a basic collection type.
As a correction to the answer from a horse with no name on the multi column situation, where you might want to aggregate still on a third column.
If the rn is left as a where condition, other columns would be incorrect.
The simplest way to handle multiple listagg's is to use 1 with per column.