Eliminate Duplicates Using Oracle Listagg Function

How to remove duplicate values from a aggregate list when using LISTAGG() in oracle SQL?

For a simple query, you can use a subquery:

SELECT GRADE, listagg(name, ';') within group (order by name) as names
FROM (SELECT DISTINCT GRADE, st_name || ';' || st_last_name as name
      FROM STUDENTS
     ) s
GROUP BY GRADE;

I find it odd that you are using a semi-colon both to separate st_name and st_last_name and to separate the different values. I would expect different separators to be used -- 'john;smith;jane;doe;' is harder to read than 'john,smith;jane,doe'.

How I remove duplicates from listagg

First select DISTINCT values you need, then apply LISTAGG to them. Here's an example based on Scott's schema.

SQL> -- Duplicate jobs within the department
SQL> select deptno, listagg(job, ', ') within group (order by job) jobs
  2  from emp
  3  group by deptno;

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

SQL>
SQL> -- This won't work - DISTINCT can't be used in LISTAGG
SQL> select deptno, listagg(distinct job, ', ') within group (order by job) jobs
  2  from emp
  3  group by deptno;
select deptno, listagg(distinct job, ', ') within group (order by job) jobs
               *
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function

SQL>
SQL> -- So - select distinct jobs first, then apply LISTAGG to it
SQL> select x.deptno, listagg(x.job, ', ') within group (order by x.job) jobs
  2  from (select distinct deptno, job
  3        from emp) x
  4  group by x.deptno;

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

SQL>

Oracle SQL: How to remove duplicate in listagg

Maybe just create the SUM of the SNR/SMR/Membrane columns, group them by name, and replace the numbers with the strings that you want to see in the output.

Query (first step ...)

select name
, sum( snr_column ), sum( smr_column ), sum( membrane_column ) 
from original 
group by name
;

-- output
NAME    SUM(SNR_COLUMN)     SUM(SMR_COLUMN)     SUM(MEMBRANE_COLUMN)
2       1                   1                   2
3       null                2                   1
1       2                   2                   null    

Replace the sums, concatenate, remove the trailing comma with RTRIM()

select 
  name 
, rtrim( 
     case when sum( snr_column ) >= 1 then 'SNR, ' end
  || case when sum( smr_column ) >= 1 then 'SMR, ' end 
  || case when sum( membrane_column ) >= 1 then 'Membrane' end 
  , ' ,'
  ) as technology
from original 
group by name
order by name
;
-- output
NAME    TECHNOLOGY
1       SNR, SMR
2       SNR, SMR, Membrane
3       SMR, Membrane

Code the CASEs in the required order.
DBfiddle

SQL listagg and concat, remove duplicates

A runner can have multiple nationalities and participate in multiple races. You join the two tables, although a particular nationality isn't linked to a particular race. Thus you are generating an undesired cartesian product. The problem is hence that you are joining entities that are not completely related.

Joining all involved tables and then aggregate the inflated intermediate result is a commom mistake. Instead aggregate first in order to get to the entities that you really want to join.

What you want to do is show a runner with their race list and their nationality list. Create these lists and then join them:

select
  c.n_coureur,
  c.nom,
  c.prenom,
  c.annee_prem,
  c.annee_naissance,
  pc.participations, 
  an.nationalites 
from tdf_coureur c
left join 
(
  select
    n_coureur,
    listagg(annee || '|' || n_equipe || '|' || n_sponsor || '|' || n_dossard || '|' || jeune || '|' || valide, ';') 
      within group (order by n_coureur, nom, prenom, annee_prem, annee_naissance) as participations
  from tdf_parti_coureur 
  group by n_coureur
) pc using(n_coureur)
left join 
(
  select
    n_coureur,
    listagg(code_cio || '|' || annee_debut || '|' || annee_fin, ';') 
      within group (order by n_coureur, nom, prenom, annee_prem, annee_naissance) as nationalites 
  from tdf_app_nation
  group by n_coureur
) an using(n_coureur)
order by c.n_coureur;

Oracle SQL Listagg remove duplicates with case statement conditions

LISTAGG is an aggregate function. If you apply it to a column, then you need to specify in the query what columns you're grouping by. Typically that is all the columns that don't have an aggregate function.
I didn't test since there is no sample data for the dept table nor the person_roles table but this is probably the issue

SELECT p.person, r.role as myrole, listagg(d.dept, ', ') within group (order by d.dept) as dept_list,
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as myaccess
  FROM person_role pr 
  JOIN person p ON p.id = pr.person_id 
  JOIN myrole r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_role pr 
    JOIN person p ON p.id = pr.person_id 
    JOIN myrole r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id 
left join dept d on d.id = pr.dept_id
GROUP BY
  p.person, 
  r.role, 
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END
ORDER BY p.person

How to eliminate duplicates from multiple LISTAGG 'ed columns in Oracle

You can do this by taking advantage of the fact the listagg() ignores NULL values. So, use row_number() to enumerate duplicates and then just pull in the first value:

SELECT grantee,
       LISTAGG(CASE WHEN seqnum_granted_role = 1 THEN granted_role END, '-')
           WITHIN GROUP (ORDER BY granted_role) AS granted_role,
       LISTAGG(CASE WHEN seqnum_privilege = 1 THEN privilege END, '-')
           WITHIN GROUP (ORDER BY privilege) AS privs,
       owner,
       table_name
FROM (SELECT grantee, granted_role, privilege,
             owner, table_name,
             ROW_NUMBER() OVER (PARTITION BY grantee, owner, table_name, granted_role
                                ORDER BY grantee
                               ) as seqnum_granted_role,
             ROW_NUMBER() OVER (PARTITION BY grantee, owner, table_name, privilege
                                ORDER BY grantee
                               ) as seqnum_privilege
       FROM sys.privs
     ) p
GROUP BY grantee, owner, table_name;

Remove duplicates from an already aggregated LISTAGG using LISTAGG

You can use the below to unlistagg and remove the duplicate and then listagg again.The DB Fiddle here

            WITH data
         AS (SELECT id,
                    Listagg(products, '|')
                      within GROUP (ORDER BY products) PRODUCTS,
                    SUM(amount)                        SUM_AMT
             FROM   (SELECT *
                     FROM   table_one
                     UNION
                     SELECT *
                     FROM   table_two)
             GROUP  BY id),
         d2
         AS (SELECT DISTINCT id,
                             Regexp_substr(products, '[^|]+', 1, column_value) AS
                             products,
                             sum_amt
             FROM   data
                    cross join TABLE(Cast(MULTISET (SELECT LEVEL
                                              FROM   dual
                                              CONNECT BY LEVEL <=
                                              Regexp_count(products,
                                              '[^|]+'))
                                                       AS
    sys.ODCINUMBERLIST)))
    SELECT id,
           Listagg(products, '|')
             within GROUP (ORDER BY id) PRODUCTS,
           sum_amt
    FROM   d2
    GROUP  BY id,
              sum_amt; 

---

Related Topics