6 Replies Latest reply: Jul 31, 2013 9:08 AM by Frank Kulash RSS

    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

        • 1. Re: Listagg, view distinct values
          Pacmann

          Hi,

          Not sure whether it works with listagg, but it does with wm_concat :

           

          SQL> with t as (

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

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

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

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

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

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

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

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

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

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

          12  select 'C', 'z' from dual)

          13  select wm_concat(distinct f2)

          14  from t

          15  group by f1

          16  /

           

           

          WM_CONCAT(DISTINCTF2)

          ----------------------------------------------

          x,y,z

          x,y

          x,y,z

          • 2. Re: Listagg, view distinct values
            KjetilSkotheim

            Thanks! :-)

             

            http://psoug.org/definition/WM_CONCAT.htm    says "Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems."

             

            But even so it is at least very useful in ad-hoc throw away queries.

            • 3. Re: Listagg, view distinct values
              ramoradba

              Distinct LISTAGG

               

              IMO, this is better than that !

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

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

              • 4. Re: Listagg, view distinct values
                Pacmann

                Actually i don't have any 11gR2 to test listagg, so i just meant that you should try adding the distinct in the listagg parameter (because it worked on wm_concat, which looks similar )

                • 5. Re: Listagg, view distinct values
                  KjetilSkotheim

                  Maybe in this example. But if f1 and f2 was large expressions instead of short column names you would want to avoid repeating them.

                   

                  ...Although making a sub-query to give f1 and f2 short aliases would work in that case, that would mean complicating stuff too. So a short keyword like distinct in wm_concat would be very welcome in listagg too, I'm almost sure it doesn't exist.

                  • 6. Re: Listagg, view distinct values
                    Frank Kulash

                    Hi,

                     

                    Sorry, LISTAGG doesn't do DISTINCT.  If you want to use LISTAGG, then you have to do a sub-query first, to get distinct values.

                     

                    If you're tempted to use WM_CONCAT, then copy the STRAGG function from

                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402

                    STRAGG seems to do exactly what WM_CONCAT does.  (WM_CONCAT is undocumented, so we don't actually know what it does.)   STRAGG is very handy, but, unfortunately, it doesn't put its results in order.

                     

                    SYS_CONNECT_BY_PATH (shown in the same AskTom page mentioned above) can be used to get distinct, ordered results.  You would probably need a sub-query to get the results you want, so, if you have Oracle 11.2 or higher, you might as well use LISTAGG.