LISTAGG in Oracle to return distinct values

Upcoming Oracle 19c will support DISTINCT with LISTAGG.

LISTAGG with DISTINCT option:

This feature is coming with 19c:

SQL> select deptno, listagg (distinct sal,', ') within group (order by sal)  
  2  from scott.emp  
  3  group by deptno;  

EDIT:

Oracle 19C LISTAGG DISTINCT

The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword. 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. With the new DISTINCT keyword, duplicate values can be removed from the specified expression before concatenation into a single string. This removes the need to create complex query processing to find the distinct values prior to using the aggregate LISTAGG function. With the DISTINCT option, the processing to remove duplicate values can be done directly within the LISTAGG function. The result is simpler, faster, more efficient SQL.

Further refining @YoYo's correction to @a_horse_with_no_name's row_number() based approach using DECODE vs CASE (i saw here). I see that @Martin Vrbovsky also has this case approach answer.

select
  col1, 
  listagg(col2, ',') within group (order by col2) AS col2_list,
  listagg(col3, ',') within group (order by col3) AS col3_list,
  SUM(col4) AS col4
from (
  select
    col1, 
    decode(row_number() over (partition by col1, col2 order by null),1,col2) as col2,
    decode(row_number() over (partition by col1, col3 order by null),1,col3) as col3
  from foo
)
group by col1;

I neded a DISTINCT version of this and got this one working out.

RTRIM(REGEXP_REPLACE(
                       (value, ', ') WITHIN GROUP( ORDER BY value)), 
                            '([^ ]+)(, \1)+','\1'),', ') 

One annoying aspect with LISTAGG is that if the total length of concatenated string exceeds 4000 characters( limit for VARCHAR2 in SQL ), the below error is thrown, which is difficult to manage in Oracle versions upto 12.1

ORA-01489: result of string concatenation is too long

A new feature added in 12cR2 is the ON OVERFLOW clause of LISTAGG. The query including this clause would look like:

SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

The above will restrict the output to 4000 characters but will not throw the ORA-01489 error.

These are some of the additional options of ON OVERFLOW clause:

• ON OVERFLOW TRUNCATE 'Contd..' : This will display 'Contd..' at the end of string (Default is ... )
• ON OVERFLOW TRUNCATE '' : This will display the 4000 characters without any terminating string.
• ON OVERFLOW TRUNCATE WITH COUNT : This will display the total number of characters at the end after the terminating characters. Eg:- '...(5512)'
• ON OVERFLOW ERROR : If you expect the LISTAGG to fail with the ORA-01489 error ( Which is default anyway ).

Using SELECT DISTINCT ... as part of a Subquery before calling LISTAGG is probably the best way for simple queries, as noted by @a_horse_with_no_name

However, in more complex queries, it might not be possible, or easy, to accomplish this. I had this come up in a scenario that was using top-n approach using an analytic function.

So I found the COLLECT aggregate function. It is documented to have the UNIQUE or DISTINCT modifier available. Only in 10g, it quietly fails (it ignores the modifier without error). However, to overcome this, from another answer, I came to this solution:

SELECT
  ...
  (
    SELECT LISTAGG(v.column_value,',') WITHIN GROUP (ORDER BY v.column_value)
    FROM TABLE(columns_tab) v
  ) AS columns,
  ...
FROM (
  SELECT
    ...
    SET(CAST(COLLECT(UNIQUE some_column ORDER BY some_column) AS tab_typ)) AS columns_tab,
    ...
)

Basically, by using SET, I remove the duplicates in my collection.

You would still need to define the tab_typ as a basic collection type, and in the case of a VARCHAR, this would be for example:

CREATE OR REPLACE type tab_typ as table of varchar2(100)
/

Also 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 (or more) columns:

select
  col1, 
  listagg(CASE rn2 WHEN 1 THEN col2 END, ',') within group (order by col2) AS col2_list,
  listagg(CASE rn3 WHEN 1 THEN col3 END, ',') within group (order by col3) AS col3_list,
  SUM(col4) AS col4
from (
  select
    col1, 
    col2,
    row_number() over (partition by col1, col2 order by null) as rn2,
    row_number() over (partition by col1, col3 order by null) as rn3
  from foo
)
group by col1;

If you would leave the rn = 1 as a where condition to the query, you would aggregate other columns incorrectly.

select col1, listaggr(col2,',') within group(Order by col2) from table group by col1 meaning aggregate the strings (col2) into list keeping the order n then afterwards deal with the duplicates as group by col1 meaning merge col1 duplicates in 1 group. perhaps this looks clean and simple as it should be and if in case you want col3 as well just you need to add one more listagg() that is select col1, listaggr(col2,',') within group(Order by col2),listaggr(col3,',') within group(order by col3) from table group by col1

Very simple - use in your query a sub-query with a select distinct:

SELECT question_id,
       LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM
       (SELECT distinct question_id, element_id
       FROM YOUR_TABLE)
GROUP BY question_id;