6 Replies Latest reply on Jul 31, 2013 2:08 PM by Frank Kulash

    Listagg, view distinct values

    KjetilSkotheim

      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