Eliminate duplicates in ListAgg (Oracle)

Question

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.

Answer 1

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.

Answer 2

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?

Answer 3

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
);

Answer 4

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.

Answer 5

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.

Answer 6

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
)

Answer 7

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;

Answer 8

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(+);

Answer 9

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);