Forum Stats

  • 3,728,531 Users
  • 2,245,647 Discussions
  • 7,853,577 Comments

Discussions

SQL listagg with distinct

user9311128
user9311128 Member Posts: 14 Blue Ribbon

Hi,

I need to build a select query that concatenates the unique values and distinct the duplicate values in a column. Can anyone help please?

I tried with listagg, concatenates well but not able to distinct.

Data Source:

Data Output Expected:


Thank you

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond
    Accepted Answer

    Hi, @User939212-Oracle

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

    listagg, concatenates well but not able to distinct.

    That depends on the Oracle version. LISTAGG has the DISTINCT option in version 19, but not in 12. To get those results in Oracle 122 (or earlier), you need to do the DISTINCT in a separate sub-query before using LISTAGG. Alternatively, you can use SYS_CONNECT_BY_PATH.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond
    Accepted Answer

    Hi, @User939212-Oracle

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.

    listagg, concatenates well but not able to distinct.

    That depends on the Oracle version. LISTAGG has the DISTINCT option in version 19, but not in 12. To get those results in Oracle 122 (or earlier), you need to do the DISTINCT in a separate sub-query before using LISTAGG. Alternatively, you can use SYS_CONNECT_BY_PATH.

  • user9311128
    user9311128 Member Posts: 14 Blue Ribbon

    Yes. Thank you

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond
    edited April 7

    Hi,

    If the list is ordered such that duplicates are always consecutive, then you could use REGEXP_REPLACE to remove them. For example:

    SELECT  deptno
    ,	 TRIM ( ','
    	      FROM REPLACE ( REGEXP_REPLACE ( ',' || LISTAGG (job, ',,')
    	    	   	    		   	      WITHIN GROUP (ORDER BY job)
    						  || ','
    	    	   	    		    , '(,[^,]+,)\1+'
    					    , '\1'
    					    )
    	    	   	   , ',,'
    			   , ','
    			   )
    	    ) AS job_list
    FROM     scott.emp
    GROUP BY deptno
    ORDER BY deptno
    ;
    

    Output:

     DEPTNO JOB_LIST
    ------- ------------------------------
         10 CLERK,MANAGER,PRESIDENT
         20 ANALYST,CLERK,MANAGER
         30 CLERK,MANAGER,SALESMAN
    

    When removing duplicates, you need to be careful to compare whole entries. You don't way to remove cases where part of one entry matches the adjacent entry, e.g. 'ABC,ABCD'.

    user9311128
  • user9311128
    user9311128 Member Posts: 14 Blue Ribbon

    Thank you, it worked for me!

Sign In or Register to comment.