Forum Stats

  • 3,815,606 Users
  • 2,259,059 Discussions
  • 7,893,185 Comments

Discussions

LISTAGG with DISTINCT option

135

Comments

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy

    This would be useful. I can already thing of different ways that I can use this. + for me.

    ApexBine
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy

    Oh wow....I thought I was the only one that ran into this issue.  Yes using CTE (common-table-expressions) via sub-query factoring (aka WITH clause) would help solve the OP's problem but having the option added to the grammar of the LISTAGG function would help IMMENSELY.


    Once I've found out about this future I use it when I need to concatenate a list of values comma-delimited.

    There is another method or sister function of the XML variety (XMLAGG(XMLELEMENT .....

    I definitely vote for this.

    ApexBine
  • Nikolaus Thiel
    Nikolaus Thiel Member Posts: 19
    edited Oct 6, 2016 9:35AM

    I would love to see listagg(distinct,_,_) to be implemented in the next release.

    Please note that first using listagg and then regexp_replace works for small data sets only, because listagg returns VARCHAR2 which allows 4000 characters only.

    Here an example:

    SQL> select * from v$version;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE    11.2.0.4.0      Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    SQL> with data as  (select mod(level,2) ID from dual connect by level <10)
    ,data_list as (select listagg(ID,',') within group (order by ID) ID_LIST from data)
    select regexp_replace(ID_LIST,'([^,]+)(,\1)+', '\1') from data_list
    ;

    REGEXP_REPLACE(ID_LIST,'([^,]+)(,\1)+','\1')
    --------------------------------------------
    0,1

    SQL> with data as  (select mod(level,2) ID from dual connect by level <10000)
    ,data_list as (select listagg(ID,',') within group (order by ID) ID_LIST from data)
    select regexp_replace(ID_LIST,'([^,]+)(,\1)+', '\1') from data_list
    ;

    ERROR at line 1: ORA-01489: result of string concatenation is too long
    ApexBine
  • Sven W.
    Sven W. Member Posts: 10,534 Gold Crown

    I would love to see listagg(distinct,_,_) to be implemented in the next release.

    Please note that first using listagg and then regexp_replace works for small data sets only, because listagg returns VARCHAR2 which allows 4000 characters only.

    Here an example:

    SQL> select * from v$version;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE    11.2.0.4.0      Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    SQL> with data as  (select mod(level,2) ID from dual connect by level <10)
    ,data_list as (select listagg(ID,',') within group (order by ID) ID_LIST from data)
    select regexp_replace(ID_LIST,'([^,]+)(,\1)+', '\1') from data_list
    ;

    REGEXP_REPLACE(ID_LIST,'([^,]+)(,\1)+','\1')
    --------------------------------------------
    0,1

    SQL> with data as  (select mod(level,2) ID from dual connect by level <10000)
    ,data_list as (select listagg(ID,',') within group (order by ID) ID_LIST from data)
    select regexp_replace(ID_LIST,'([^,]+)(,\1)+', '\1') from data_list
    ;

    ERROR at line 1: ORA-01489: result of string concatenation is too long

    Please note that in 12.2 listagg was enhanced slightly. It still only supports up to 4000 chars, however you can now specify what should happen when the list gets to long.

    From the doc: Oracle Database 12.2 New Features

    "Enhanced LISTAGG Functionality

    LISTAGG aggregates the values of a column by concatenating them into a single string. New functionality has been added for managing situations where the length of the concatenated string is too long.

    Developers can now control the process for managing overflowing LISTAGG aggregates. This increases the productivity and flexibility of this aggregation function."

    The new syntax looks like this:

    ON OVERFLOW TRUNCATE WITH COUNT

    ApexBine
  • Please note that in 12.2 listagg was enhanced slightly. It still only supports up to 4000 chars, however you can now specify what should happen when the list gets to long.

    From the doc: Oracle Database 12.2 New Features

    "Enhanced LISTAGG Functionality

    LISTAGG aggregates the values of a column by concatenating them into a single string. New functionality has been added for managing situations where the length of the concatenated string is too long.

    Developers can now control the process for managing overflowing LISTAGG aggregates. This increases the productivity and flexibility of this aggregation function."

    The new syntax looks like this:

    ON OVERFLOW TRUNCATE WITH COUNT

    Sven W. wrote:The new syntax looks like this:ON OVERFLOW TRUNCATE WITH COUNT

    Truncating and thus modifying the result isntead of an error sounds scary. Though there might be occasion where this could be usefull.

    Better would be to not add duplicates to the output (DISTINCT) and to increase the varchar2 length to varchar2(32767) as in PL/SQL.

  • Raj Jamadagni
    Raj Jamadagni Member Posts: 511
    Sven W. wrote:The new syntax looks like this:ON OVERFLOW TRUNCATE WITH COUNT

    Truncating and thus modifying the result isntead of an error sounds scary. Though there might be occasion where this could be usefull.

    Better would be to not add duplicates to the output (DISTINCT) and to increase the varchar2 length to varchar2(32767) as in PL/SQL.

    If it needs to be extended to varchar2(32767) why not provide an overloaded function to return a CLOB? I'd surely vote that that.

  • User_5AM5X
    User_5AM5X Member Posts: 1 Red Ribbon

    Here's a much better solution all-round.      Right-click and 'Translate to English'.

    "Oracle SQL und PL/SQL" von Carsten Czarski: SQL LISTAGG mit CLOB-Ausgabe - kein VARCHAR2-Limit mehr

    ApexBinekcelik
  • Here's a much better solution all-round.      Right-click and 'Translate to English'.

    "Oracle SQL und PL/SQL" von Carsten Czarski: SQL LISTAGG mit CLOB-Ausgabe - kein VARCHAR2-Limit mehr

    That is great, thank you for the mention, bookmarked and I'll test it. But as you can see it occasionally gives errors, so all that might just go away if this becomes a built-in solution.

  • ApexBine
    ApexBine Member Posts: 153 Silver Badge
    Sven W. wrote:The new syntax looks like this:ON OVERFLOW TRUNCATE WITH COUNT

    Truncating and thus modifying the result isntead of an error sounds scary. Though there might be occasion where this could be usefull.

    Better would be to not add duplicates to the output (DISTINCT) and to increase the varchar2 length to varchar2(32767) as in PL/SQL.

    As always: 'it depends'.

    I can see use cases for all three: truncate, distinct and increase the length of a possible result

    Raj Jamadagni
  • NSK2KSN
    NSK2KSN Member Posts: 600 Bronze Badge

    This would definitely make queries easier and avoid an additional step to subquery distinct values before doing the LISTAGG.

    But I'd propose a slightly different format for the function. Since we already have DISTINCT functionality in some aggregate functions like COUNT, that should be consistent with LISTAGG. So I think the syntax should ideally look like -

    LISTAGG ( [DISTINCT] <column_name>  [ , <delimiter> ] )  OVER ...

    This makes it clearer that the DISTINCT is applied on the column values.

    Agreed, Sort also should be there within the LISTAGG function and then only this can be used effectively ?