Until 19c LISTAGG doesn't support DISTINCT, any workaround?

27/10/19 08:53
Several solutions exist: with REGEXP, custom PL/SQL procedure, LAG combined with DECODE…
Let's see + and - of each one…
  • With regexp: one classical solution assuming the separator of your LISTAGG is ',':
regexp_replace(your_string, '([^,]+)(,\1)*(,|$)', '\1\3')

Since it's still manipulating VARCHAR2 the size remains limited to default DB's VARCHAR2 size, generally 4000 unless extended char mode is specified in your DB set-up.

  • With custom procedure:

Several exists on the Net, the greatest inconvenient is that you loose the WITHIN GROUP(ORDER By…) so it may not be useable in all situations, especially when you need several LISTAGG on different fields in a PIVOT…
One example:

CREATE OR REPLACE TYPE LISTAGG_DISTINCT_PARAMS AS OBJECT (item VARCHAR2(4000), separator VARCHAR2(10));
/

CREATE OR REPLACE TYPE T_ITEMS_LIST AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE TYPE T_LISTAGG_DISTINCT AS OBJECT (

w_items T_ITEMS_LIST,
w_separator VARCHAR2(10),

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LISTAGG_DISTINCT, VALUE IN LISTAGG_DISTINCT_PARAMS ) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LISTAGG_DISTINCT,
RETURN_VALUE OUT CLOB,
FLAGS IN NUMBER )
RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LISTAGG_DISTINCT, CTX2 IN T_LISTAGG_DISTINCT)
RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY T_LISTAGG_DISTINCT IS

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT) RETURN NUMBER IS
BEGIN
SCTX := T_LISTAGG_DISTINCT(T_ITEMS_LIST() , ',');
RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LISTAGG_DISTINCT, VALUE IN LISTAGG_DISTINCT_PARAMS) RETURN NUMBER IS
BEGIN
IF VALUE.item IS NOT NULL THEN
SELF.w_items.EXTEND;
SELF.w_items(SELF.w_items.LAST) := TO_CHAR(VALUE.item);
SELF.w_items := SELF.w_items MULTISET UNION DISTINCT SELF.w_items;
SELF.w_separator := VALUE.SEPARATOR;
END IF;
RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LISTAGG_DISTINCT, RETURN_VALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
w_result CLOB:='';
w_list_out T_ITEMS_LIST;
BEGIN
IF SELF.w_items.EXISTS(1) THEN
w_list_out := T_ITEMS_LIST();

w_list_out := SELF.w_items MULTISET UNION DISTINCT SELF.w_items;

SELECT CAST(MULTISET(SELECT * FROM TABLE(w_list_out) ORDER BY 1 ) AS T_ITEMS_LIST ) INTO w_list_out FROM DUAL;

FOR I IN w_list_out.FIRST .. w_list_out.LAST - 1
LOOP
w_result := w_result || w_list_out(I) || SELF.w_separator;
END LOOP;
w_result := w_result || w_list_out(w_list_out.LAST);

RETURN_VALUE := w_result;
ELSE
RETURN_VALUE := NULL;
END IF;

RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LISTAGG_DISTINCT, CTX2 IN T_LISTAGG_DISTINCT) RETURN NUMBER IS
BEGIN
RETURN ODCICONST.SUCCESS;
END;

END;
/

CREATE FUNCTION LISTAGG_DISTINCT (INPUT LISTAGG_DISTINCT_PARAMS) RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING T_LISTAGG_DISTINCT;
/



  • With DECODE over LAG:
You need as subquery in which you will replace the column you want to aggregate by NULL if equals to the one of the previous line:
DECODE(LAG(your_string) OVER(PARTITION BY … ORDER BY …), your_string, null, your_string) as your_string

This is taking advantage of the fact that LISTAGG skips NULL values.

SELECT
listagg(owner, ',') within group(order by owner) as owners_list
FROM (
select decode(lag(owner) over(partition by owner order by owner), owner, null, owner) as owner from SYS.ALL_OBJECTS
)
;


Or JSON_ARRAYAGG which allows to return CLOB:

SELECT
regexp_replace(
regexp_replace( json_arrayagg(owner order by owner absent on null returning clob)
, '^[[](.*)[]]$', '\1')
, '"([[:alnum:]|_]+)"(,{0,1})', '\1\2') as owners_list
FROM (
select decode(lag(owner) over(partition by owner order by owner), owner, null, owner) as owner from SYS.ALL_OBJECTS
)
;




OWNERS_LIST
AD,APEX_050100,APEX_180100,AV,CO,CTXSYS,DD,HR,LIVESQL,MDSYS,OE,OLAPSYS,OLYM,ORDDATA,ORDPLUGINS,ORDSYS,ORDS_METADATA,PROJECTS,PUBLIC,SCOTT,SH,SQL_USKYZYWUDCBJURJQLPZVJMJLD,SYS,WORLD,XDB


  • With undocumented wm_concat function:

NO GO !!! Undocumented is enough to justify to not use it and it doesn't exist anymore in 12c.