Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to eliminate duplicates in the results and don't seem to be able to do that. Here is an example.

CREATE TABLE ListAggTest AS (
  SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual 
     CONNECT BY rownum<=6
  );
SELECT * FROM ListAggTest;

      NUM1 NUM2
---------- ---------------------
         1 2
         2 2                    << Duplicate 2
         3 3
         4 4
         5 5
         6 6

What I want to see is this:

      NUM1 NUM2S
---------- --------------------
         1 2-3-4-5-6
         2 2-3-4-5-6
         3 2-3-4-5-6
         4 2-3-4-5-6
         5 2-3-4-5-6
         6 2-3-4-5-6

Here is a listagg version that is close, but doesn't eliminate duplicates.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s 
FROM ListAggTest;

I have a solution, but it's worse than continuing to use the custom aggregate function.

share|improve this question
    
Should order by null be order by Num2 or am I getting confused? –  Jack Douglas Mar 8 '12 at 19:09
    
@Jack - It makes no difference to the duplicate elimination. Depending on your use, it may be desirable. –  Leigh Riffel Mar 8 '12 at 19:18
    
ah, yes - so you didn't mean you needed them ordered 2-3-4-5-6 necessarily. It did make a difference for my regex solution which I've fixed as it relied on dupes being adjacent. –  Jack Douglas Mar 8 '12 at 20:21
    
@JackDouglas Exactly. –  Leigh Riffel Mar 8 '12 at 20:24
add comment

10 Answers 10

up vote 14 down vote accepted

You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg:

SELECT Num1, 
       RTRIM(
         REGEXP_REPLACE(
           (listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()), 
           '([^-]*)(-\1)+($|-)', 
           '\1\3'),
         '-') Num2s 
FROM ListAggTest;

This could be tidier if Oracle's regex flavour supported lookahead or non-capturing groups, but it doesn't.

However this solution does avoid scanning the source more than once.

share|improve this answer
    
I think you need a question mark after the first group: '(^|-)?([^-]*)(-\2)+($|-)' –  MK01 Mar 8 '12 at 16:37
    
Can you explain why you think that? You did draw my attention to an error though - I'm pretty sure the order by null was wrong –  Jack Douglas Mar 8 '12 at 19:05
    
Because this fails: select REGEXP_REPLACE( '0-0-1-1-2-2-2-3-3-3-3-3-4-4-4', '(^|-)([^-]*)(-\2)+($|-)', 'a\1b\2c-') from dual; So the 2 possible alternatives that I came up with is to either add that question maro or better loose the last $/- check as this select REGEXP_REPLACE( '0-0-1-1-42-2-2-3-3-3-3-3-4-4-4', '(^|-)([^-]*)(-\2)+', '\1\2') from dual; –  MK01 Mar 8 '12 at 20:20
    
Excellent point! The lack of lookaround makes this tricky to fix. Rather than add a '?' I think it is better to remove the first capture as in my edit: we then rely on the fact that a string like '-22-2-' (which would match) can never occur because Num2 is ordered and each Num2 therefore has at least as many digits as the previous Num2. Thanks for spotting this and providing the example :-) –  Jack Douglas Mar 9 '12 at 7:22
    
Do you see any problem with '(^|-)([^-]*)(-\2)+', '\1\2') –  MK01 Mar 9 '12 at 13:43
show 1 more comment

As far as I can see, with the currently available language specification, this is the shortest to achieve what you want if it must be done with listagg.

select distinct
       a.Num1, 
       b.num2s
  from listaggtest a cross join (
       select listagg(num2d, '-') within group (order by num2d) num2s 
       from (
         select distinct Num2 num2d from listaggtest
       )
      ) b;

What was your solution that was worse than the custom aggregate solution?

share|improve this answer
    
This works, but has to do two full table scans. –  Leigh Riffel Jan 19 '11 at 0:25
add comment

Here was my solution to the problem which in my opinion isn't as nice as using our custom aggregate function which already exists.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s FROM (
  SELECT Num1, DECODE(ROW_NUMBER() OVER (PARTITION BY Num2 ORDER BY NULL),
     1,Num2,NULL) Num2 FROM ListAggTest
);
share|improve this answer
add comment

Create a custom aggregate function to do this.

Oracle database provides a number of pre-defined aggregate functions such as MAX, MIN, SUM for performing operations on a set of records. These pre-defined aggregate functions can be used only with scalar data. However, you can create your own custom implementations of these functions, or define entirely new aggregate functions, to use with complex data—for example, with multimedia data stored using object types, opaque types, and LOBs.

User-defined aggregate functions are used in SQL DML statements just like the Oracle database built-in aggregates. Once such functions are registered with the server, the database simply invokes the aggregation routines that you supplied instead of the native ones.

User-defined aggregates can be used with scalar data as well. For example, it may be worthwhile to implement special aggregate functions for working with complex statistical data associated with financial or scientific applications.

User-defined aggregates are a feature of the Extensibility Framework. You implement them using ODCIAggregate interface routines.

share|improve this answer
add comment

Use WMSYS.WM_Concat instead.

SELECT Num1, Replace(Wm_Concat(DISTINCT Num2) OVER (), ',', '-')
FROM ListAggTest;

Note: This function is undocumented and unsupported. See https://forums.oracle.com/forums/message.jspa?messageID=4372641#4372641.

share|improve this answer
1  
If you call Oracle support and you are using wm_concat (even if you argue the wm_concat is not itself causing the problem) they'd have grounds for refusing to help because it is undocumented and unsupported - not the case if you use a custom aggregate or any other supported feature. –  Jack Douglas Nov 25 '11 at 19:23
add comment

You could also use a collect statement and then write a custom pl/sql function that converts the collection to a string.

CREATE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
CREATE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

select cast(collect(distinct num2 order by num2) as varchar2_ntt) 
from listaggtest

You can use distinct and order by in a collect clause but if combined the distinct won't work as of 11.2.0.2 :(

Workaround could be a subselect:

select collect(num2 order by num2) 
from 
( 
    select distinct num2 
    from listaggtest
)
share|improve this answer
    
I fail to see how a custom pl/sql function would be better than a custom aggregate function. The resulting SQL is certainly simpler for the latter. Since this problem was on 11.2.0.2 the subselect would add an additional scan which I was trying to avoid. –  Leigh Riffel Sep 16 '11 at 12:13
    
I'd say a PL/SQL function called ONCE to convert the collection into a string could be better than the aggregate function called thousands of times. I think this would reduce context switches a lot. –  Nico Sep 20 '11 at 11:11
    
Your theory sounds good and was one reason I was trying to avoid the custom aggregate function and was preferring a built in aggregate function like LISTAGG. If you'd like to do some timing comparisons I'd be interested in the results. –  Leigh Riffel Sep 20 '11 at 12:20
add comment

Why doesn't this one modification to your code do what you want? I'm curious because I'm sure it's the first thing you tried:

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s 
FROM ListAggTest;

to

SELECT DISTINCT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s 
FROM ListAggTest;
share|improve this answer
    
I want to eliminate the 2 from the listagg results, a distinct like this does not eliminate any records (correct), but also does not eliminate the 2 from the listagg results (incorrect). –  Leigh Riffel Jan 19 '11 at 0:17
    
@Leigh ~ Ohhhh, and a temptable join won't help here either huh? –  jcolebrand Jan 19 '11 at 0:19
    
I could put the data in a global temporary table, but then I definitely would not have a single statement solution and would definitely have two full table scans. –  Leigh Riffel Jan 19 '11 at 0:32
add comment
SELECT Num1, listagg(Num2,'-') WITHIN GROUP
(ORDER BY num1) OVER () Num2s FROM 
(select distinct num1 from listAggTest) a,
(select distinct num2 from ListAggTest) b
where num1=num2(+);
share|improve this answer
    
This does return the correct results for the data given, but has an incorrect assumption. Num1 and Num2 are unrelated. Num1 could just as well be Char1 containing values a,e,i,o,u,y. Reguardless, this solution requires two full scans of the table defeating the whole purpose of using the aggregate function. If the solution permitted two table scans, then this would be preferred (with the sample data it has a lower cost than anything else). SELECT Num1, ( SELECT LISTAGG(Num2) WITHIN GROUP (ORDER BY Num2) FROM (SELECT distinct Num2 FROM listAggTest) ) Num2 FROM ListAggTest; –  Leigh Riffel Apr 9 '13 at 12:46
add comment

SQL was designed as simple language, very close to english. So why don't you write it as in english?

  1. eliminate duplicates on num2 & use listagg as aggregate function - not analytic, to compute concat on string
  2. join in to original, as you want one result row for one input

select num1, num2s
  from (select num2,
               listagg(num2, '-') within group(order by num2) over() num2s
          from listaggtest
         group by num2
       )
  join listaggtest using (num2);

share|improve this answer
    
Thank you for your response. This solution requires two full table scans, but more importantly does not return the correct results. –  Leigh Riffel Feb 10 '11 at 14:09
    
Sorry for that, I have pasted some older and incorrect version. –  Štefan Oravec Feb 18 '11 at 0:26
add comment

The most effective solution is inner SELECT with GROUP BY, because DISTINCT and regular expressions are slow as hell.

SELECT num1, LISTAGG(num2, '-') WITHIN GROUP (ORDER BY num2) AS num2s
    FROM (SELECT num1, num2
              FROM ListAggTest
              GROUP BY num1, num2)
    GROUP BY num1;

This solution is pretty simple - first you get all unique combinations of num1 and num2 (inner SELECT) and then you get the string of all num2 grouped by num1.

share|improve this answer
    
This query does not return the requested results. It returns the same results as SELECT * FROM ListAggTest;. –  Leigh Riffel Dec 9 '13 at 14:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.