Using Oracle’s ListAgg with distinct

Oracle’s 11g ListAgg function is a great way to concat multiple rows into a single column. However, it has a major limitation (in addition to the light documentation) – you can’t use distinct in ListAgg, a serious limitation. Online solutions suggest subqueries or regular expressions. I decided to write my own aggregate function to replace ListAgg.
Here goes:

  • First of all, I created the type specification:
    create or replace type TextAggregation as object
    (
      aggString VARCHAR2(32767), 
      static function ODCIAggregateInitialize(sctx IN OUT TextAggregation) 
        return number,
      member function ODCIAggregateIterate(self IN OUT TextAggregation, 
        value IN VARCHAR2) return number,
      member function ODCIAggregateTerminate(self IN TextAggregation, 
        returnValue OUT VARCHAR2, flags IN VARCHAR2) return number,
      member function ODCIAggregateMerge(self IN OUT TextAggregation, 
        ctx2 IN TextAggregation) return number
    );
    /
    
  • Then, the type body:
    create or replace type body TextAggregation is 
    static function ODCIAggregateInitialize(sctx IN OUT TextAggregation) 
    return number is 
    begin
      sctx := TextAggregation('');
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateIterate(self IN OUT TextAggregation, value IN VARCHAR2) return number is
      location number;
    begin
    	location := instr(',' || aggString || ',' , ',' || value || ',');
    	
    	if location > 0 then
    		return ODCIConst.Success;
    	end if;
    	
      if (aggString is null) then
        aggString := value;
      else
        aggString := aggString || ',' || value;
      end if;
      
    	return ODCIConst.Success;
    end;
    
    member function ODCIAggregateTerminate(self IN TextAggregation, 
        returnValue OUT VARCHAR2, flags IN VARCHAR2) return number is
    begin
      returnValue := self.aggString;
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateMerge(self IN OUT TextAggregation, ctx2 IN TextAggregation) return number is
    begin
      self.aggString := ctx2.aggString;
      return ODCIConst.Success;
    end;
    end;
    /
    
  • And then the actual function
    CREATE or replace FUNCTION MyListAgg (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING TextAggregation;
    /
    

20 thoughts on “Using Oracle’s ListAgg with distinct

  1. Hi

    I’m french and my company has Oracle 11gR2 (previous version was 10g).
    i can’t use wm_concat and listagg is… not good with big database.
    I’m agree with you, listagg without DISTINCT is a serious limitation.
    And subqueries is not good (time ans ressources increased).

    So, I use your function and it’s better than Oracle Listagg.
    I don’t understand their reflection (it’s not an evolution…)

    So, I want to say to you a great THANKS for this function 🙂
    I will win a lot of time

    • Happy to help. Do note that this aggregate function does not support parallel processing, so don’t use the parallel hint with it.

  2. Hi,

    I’m using your function with a Linguistic Sorting by set nls_comp=LINGUISTIC & set nls_sort=BINARY_AI; but the result return from MyListAgg function is automatically convert to lower case while it is upper case in the database. Do you have any idea about this problem?

    Thank you very much for your support.

    Regards.

      • HI, i’m very sure that we have no to_lower in our code. You can perform a simple test just by execute these SQL below before running function :

        alter session set nls_comp=LINGUISTIC;
        alter session set nls_sort=BINARY_AI;

        Then when reset to this the function work properly :

        alter session set nls_comp=BINARY;
        alter session set nls_sort=BINARY;

        • I just run it on my environment, and it worked perfectly with nls_comp=LINGUISTIC. Are you sure you’re getting different values from the aggregate function based on these 2 parameters only?

  3. Is use of var number is necessary ?
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT toolbox_string_agg_type_unq,
    value IN varchar2 )
    RETURN NUMBER
    IS
    BEGIN
    if instr(self.total,value)=0 then
    self.total := self.total || ‘,’ || value;
    elsif self.total is null then
    self.total := self.total || ‘,’ || value;
    end if;
    RETURN ODCIConst.Success;
    END;

  4. Hi,

    Can we use order by in your function. Your function is working well. But its not returning in the order we want. For e.g.

    I have values A,A,K,L,K,Z,B,B, its returning as
    A,K,L,Z,B

    I want
    A,B,K,L

    In normal Listagg funciton, we can give order by, is it possible to add order by in your funciton.

Leave a Reply

Your email address will not be published. Required fields are marked *