5 Replies Latest reply: Jun 5, 2012 7:44 PM by Hugh Nelson RSS

    Distinct LISTAGG

    user9208525
      Hi Everyone,

      I'm trying to get a distinct comma concatenated list of values from a query. Here's something close to want I want to do:

      select
      dummy,
      LISTAGG(col1, ',') within group (order by col1),
      LISTAGG(col2, ',') within group (order by col2)
      from(
      select dummy, 'a' col1,'e' col2
      from dual
      union all
      select dummy, 'a' col1,'f' col2
      from dual
      union all
      select dummy, 'b' col1,'f' col2
      from dual
      )
      group by dummy
      ;

      DUMMY, COL1, COL2
      X     a,a,b     e,f,f

      But the problem is LISTAGG repeats the 'a' and the 'f' and I want a distinct list like 'a,b' and 'e,f'.

      I tried putting distinct in the LISTAGG clause but that didn't work out so well.

      I was thinking about making a group by clause and then using distinct, but col1 and col2 combination makes each record unique so I wanted solve the issue that way.

      I could do separate queries or window queries but I felt like there must be a better way to get LISTAGG to give me a distinct list.

      does anyone know how to accomplish this?

      -=Joe
        • 1. Re: Distinct LISTAGG
          user9208525
          Just as a follow up, I don't have the luxury of removing duplicates after because I'm hitting the ORA error that the string concatenation is too long(ORA-01489).

          So I need to eliminate the duplicates before the string gets too long.

          -=Joe
          • 2. Re: Distinct LISTAGG
            Frank Kulash
            Hi,

            This does what you requested:
            WITH     got_ds         AS
            (
                 SELECT     dummy
                 ,      col1, col2     -- If needed
                 ,     CASE
                          WHEN  ROW_NUMBER () OVER ( PARTITION BY  dummy
                                                       ,          col1
                                                       ORDER BY          0
                                          ) = 1
                          THEN  col1
                      END     AS d_col1
                 ,     CASE
                          WHEN  ROW_NUMBER () OVER ( PARTITION BY  dummy
                                                       ,          col2
                                                       ORDER BY          0
                                          ) = 1
                          THEN  col2
                      END     AS d_col2
                 FROM    table_x
            )
            SELECT       dummy
            ,       LISTAGG (d_col1, ',') WITHIN GROUP (ORDER BY d_col1)     AS col1_list
            ,       LISTAGG (d_col2, ',') WITHIN GROUP (ORDER BY d_col2)     AS col2_list
            FROM       got_ds
            GROUP BY  dummy
            ;
            Like you, I feel there must be a better way.

            The user-defined aggregate fucntion STRAGG allows DISTINCT
            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
            Unfortunately, it doesn't put the items in any particular order. (You could modify it to put the entries in order.)

            Edited by: Frank Kulash on Dec 21, 2011 4:08 PM
            • 3. Re: Distinct LISTAGG
              Tubby
              Well, i'm not really sure what you need to do with the list so i'll throw this out there as a possible alternative (it's possible it's of no use to you). The COLLECT function would return arrays (nested tables), possibly more useful, possibly less ... depending on what you're doing :)
              create table test_data as
              select *
              from
              (
                 select dummy, cast('a' as varchar2(10)) col1,cast('e' as varchar2(10)) col2
                 from dual
                 union all
                 select dummy, 'a' col1,'f' col2
                 from dual
                 union all
                 select dummy, 'b' col1,'f' col2
                 from dual
                 union all
                 select 'Y', 'b' col1,'f' col2
                 from dual
              );
               
              ME_TUBBZ?select
                2     dummy,
                3     cast(collect(distinct col1 ) as sys.odcivarchar2list) as col1,
                4     cast(collect(distinct col2 ) as sys.odcivarchar2list) as col2
                5  from test_data
                6  group by dummy
                7  /
               
              D COL1                           COL2
              - ------------------------------ ------------------------------
              X ODCIVARCHAR2LIST('a', 'b')     ODCIVARCHAR2LIST('e', 'f')
              Y ODCIVARCHAR2LIST('b')          ODCIVARCHAR2LIST('f')
               
              2 rows selected.
               
              Elapsed: 00:00:00.01
              ME_TUBBZ?
              • 4. Re: Distinct LISTAGG
                pollywog
                looks like this might be an implementation of distinct stragg with an order by.

                http://blog.sydoracle.com/2005/09/analytics-with-order-by-and-distinct.html
                • 5. Re: Distinct LISTAGG
                  Hugh Nelson
                  You can do it using XML to remove the duplicates:

                  with q1 as (
                  select dummy, 'a' col1,'e' col2
                  from dual
                  union all
                  select dummy, 'a' col1,'f' col2
                  from dual
                  union all
                  select dummy, 'b' col1,'f' col2
                  from dual
                  )
                  SELECT
                  RTRIM(
                  XMLELEMENT(
                  x
                  , XMLAGG(
                  XMLELEMENT(
                  e
                  , col1||','
                  )
                  ORDER BY col1
                  )
                  ).DELETEXML('/*/E[text()=following-sibling::E/text()]' ).EXTRACT ('/*/E/text()')
                  , ',') col1_distinct_vals
                  , RTRIM(
                  XMLELEMENT(
                  x
                  , XMLAGG(
                  XMLELEMENT(
                  e
                  , col2||','
                  )
                  ORDER BY col2
                  )
                  ).DELETEXML('/*/E[text()=following-sibling::E/text()]' ).EXTRACT ('/*/E/text()')
                  , ',') col2_distinct_vals
                  FROM q1
                  GROUP BY dummy
                  ;