Oracle Unique Listagg Values

Question

I am new to using Listagg. The following script works against it, gives me a list of values, but the list duplicates the values.

Is it possible to use Listagg to return only a unique list of values.

I use oracle 10g.

select distinct ds.catnr,ds.planqty, ds.ordnr, ds.posnr, ds.segnr, listagg(case when not li.paco is null then (select unique max(li1.paco) from leos_item li1 where li.av_part_no = li1.av_part_no) end, ', ') within group (order by pd.part_no) inq_no from oes_delsegview ds, oes_address ad, oes_opos op, oes_nrbom nr, scm_prodtyp sp, leos_item li, part_description pd where ds.delnr = ad.key and ad.adr = ds.deladr and ds.pos_o_status not in ('9', 'D') and ds.pos_c_status not in ('9', 'D') and ds.seg_o_status not in ('9', 'D') and ds.seg_c_status not in ('9', 'D') and ds.cunr in ('W31170','W31172') and ds.pos_type != 'RC' and ds.ordnr = op.ordnr and ds.posnr = op.posnr and ds.catnr = pd.catnr and ds.prodtyp = pd.prodtyp and ds.packtyp = pd.packtyp and ds.catnr = nr.p_catnr (+) and ds.prodtyp = nr.p_prodtyp (+) and ds.packtyp = nr.p_packtyp (+) and nr.c_prodtyp = sp.prodtyp (+) and sp.prodgrp (+) = 'COMP' and substr(nr.c_prodtyp,1,2) not in ('MT','LF') and nr.c_catnr = li.catnr (+) and nr.c_prodtyp = li.prodtyp (+) and nr.c_packtyp = li.packtyp (+) and pd.catnr = '9780007938797' group by ds.catnr,ds.planqty, ds.ordnr, ds.posnr, ds.segnr 

The result of my Listagg is:

 14/061127-12, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16, 14/061127-16 

What I would like to see:

 14/061127-12, 14/061127-16 

Any help would be greatly appreciated.

Answer 1

I removed the first distinct from your already group by all fields in your Select query and replaced case when with a Select query:

 select ds.catnr,ds.planqty, ds.ordnr, ds.posnr, ds.segnr, listagg((select distinct max(li1.paco) from leos_item li1 where li.av_part_no = li1.av_part_no and li.paco is not null), ', ') within group (order by pd.part_no) inq_no from oes_delsegview ds, oes_address ad, oes_opos op, oes_nrbom nr, scm_prodtyp sp, leos_item li, part_description pd where ds.delnr = ad.key and ad.adr = ds.deladr and ds.pos_o_status not in ('9', 'D') and ds.pos_c_status not in ('9', 'D') and ds.seg_o_status not in ('9', 'D') and ds.seg_c_status not in ('9', 'D') and ds.cunr in ('W31170','W31172') and ds.pos_type != 'RC' and ds.ordnr = op.ordnr and ds.posnr = op.posnr and ds.catnr = pd.catnr and ds.prodtyp = pd.prodtyp and ds.packtyp = pd.packtyp and ds.catnr = nr.p_catnr (+) and ds.prodtyp = nr.p_prodtyp (+) and ds.packtyp = nr.p_packtyp (+) and nr.c_prodtyp = sp.prodtyp (+) and sp.prodgrp (+) = 'COMP' and substr(nr.c_prodtyp,1,2) not in ('MT','LF') and nr.c_catnr = li.catnr (+) and nr.c_prodtyp = li.prodtyp (+) and nr.c_packtyp = li.packtyp (+) and pd.catnr = '9780007938797' group by ds.catnr,ds.planqty, ds.ordnr, ds.posnr, ds.segnr 

Answer 2

I used the regexp_replace function to remove duplicates in my list,

 regexp_replace( listagg((select distinct max(li1.paco) from leos_item li1 where li.av_part_no = li1.av_part_no and li.paco is not null), ', ') within group (order by pd.part_no) ,'([^,]+)(,\1)+', '\1') inq_no 

Appears to work fine.

LISTAGG in oracle to return single values

Answer 3

You can do this through replacing RegEx. Here is an example:

 -- Citations Per Year - Cited Publications main query. Includes list of unique associated core project numbers, ordered by core project number. SELECT ptc.pmid AS pmid, ptc.pmc_id, ptc.pub_title AS pubtitle, ptc.author_list AS authorlist, ptc.pub_date AS pubdate, REGEXP_REPLACE( LISTAGG ( ppcc.admin_phs_org_code || TO_CHAR(ppcc.serial_num,'FM000000'), ',') WITHIN GROUP (ORDER BY ppcc.admin_phs_org_code || TO_CHAR(ppcc.serial_num,'FM000000')), '(^|,)(.+)(,\2)+', '\1\2') AS projectNum FROM publication_total_citations ptc JOIN proj_paper_citation_counts ppcc ON ptc.pmid = ppcc.pmid AND ppcc.citation_year = 2013 JOIN user_appls ua ON ppcc.admin_phs_org_code = ua.admin_phs_org_code AND ppcc.serial_num = ua.serial_num AND ua.login_id = 'EVANSF' GROUP BY ptc.pmid, ptc.pmc_id, ptc.pub_title, ptc.author_list, ptc.pub_date ORDER BY pmid; 

Answer 4

Super simple answer solved

 select regexp_replace('14/061127-12, 14/061127-16, 14/061127-16','([^,]+)(,\1)*(,|$)', '\1\3') from dual 

->

14 / 061127-12, 14 / 061127-16

see my full answer here