This discussion is archived
6 Replies Latest reply: Jul 31, 2013 7:08 AM by Frank Kulash RSS

Listagg, view distinct values

KjetilSkotheim Newbie
Currently Being Moderated

I want only distinct values in listagg, is there a way to do that without creating a sub-query or temporary table?

 

create table whops as

select 'A' f1, 'x' f2 from dual union all

select 'A', 'x' from dual union all

select 'A', 'y' from dual union all

select 'A', 'z' from dual union all

select 'B', 'x' from dual union all

select 'B', 'y' from dual union all

select 'B', 'y' from dual union all

select 'C', 'x' from dual union all

select 'C', 'y' from dual union all

select 'C', 'y' from dual union all

select 'C', 'z' from dual;

 

select f1,listagg(f2,'+') within group (order by f2)

from whops group by f1;

 

A x+x+y+z

B x+y+y

C x+y+y+z

 

The following gives me what I want, but I'd like to know if there are simpler ways to do this:

 

select f1,listagg(f2,'+') within group (order by f2)

from (select distinct f1,f2 from whops) group by f1;

 

A x+y+z

B x+y

C x+y+z

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points