我如何从listagg中删除重复项

This is my code:

这是我的代码：

SELECT rule,
       DATASET,
       type,
       LISTAGG(source,';') within GROUP (ORDER BY source) AS source,
       TARGET, 
       LISTAGG(filter,';') within GROUP (ORDER BY filter)  AS filter
FROM demo_table;

My problem is with the two listagg() that I have used. In case of two sources and one filter it is giving duplicate filter and vice versa. What can I add to have only exact number of source and filter?

我的问题是我用过的两个listagg（）。在两个源和一个过滤器的情况下，它给出重复的过滤器，反之亦然。我可以添加什么只有源和过滤器的确切数量？

2 个解决方案

#1

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

首先选择您需要的DISTINCT值，然后将LISTAGG应用于它们。这是一个基于Scott模式的例子。

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>

#2

Use Distinct inside LISTAGG()

使用区别LISTAGG（）

SELECT rule, 
       DATASET, 
       type, 
       LISTAGG(DISTINCT source,';') within GROUP ( ORDER BY source) AS source,
       TARGET, 
       LISTAGG(DISTINCT filter,';') within GROUP ( ORDER BY filter) AS filter 
FROM demo_table;

#1