SQL listagg with distinct

user9311128

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

Frank Kulash

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.

Frank Kulash

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

Yes. Thank you

Frank Kulash

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

Thank you, it worked for me!