LISTAGG in Oracle to return distinct values

aggregate-functions, listagg, oracle, sql

Solution

19c and later:

select listagg(distinct the_column, ',') within group (order by the_column)
from the_table

18c and earlier:

select listagg(the_column, ',') within group (order by the_column)
from (
   select distinct the_column 
   from the_table
) t

If you need more columns, something like this might be what you are looking for:

select col1, listagg(col2, ',') within group (order by col2)
from (
  select col1, 
         col2,
         row_number() over (partition by col1, col2 order by col1) as rn
  from foo
  order by col1,col2
)
where rn = 1
group by col1;

Problem

I am trying to use the `LISTAGG` function in Oracle. I would like to get only the distinct values for that column. Is there a way in which I can get only the distinct values without creating a function or a procedure? ``` col1 col2 Created_by 1 2 Smith 1 2 John 1 3 Ajay 1 4 Ram 1 5 Jack ``` I need to select col1 and the `LISTAGG` of col2 (column 3 is not considered). When I do that, I get something like this as the result of `LISTAGG`: [2,2,3,4,5] I need to remove the duplicate '2' here; I need only the distinct values of col2 against col1.

Original source