如何消除列中的重复数据

天边归雁

我想消除重复并显示一次
1 j2 c; @$ R5 y1 e. v7 ?$ a8 N例如
SELECT 'apple,apple,orange'FROM dual;我想展示
. o, [# h9 D9 A- w- w0 Capple,orange再举一个例子。
SELECT 'apple,apple,apple,apple,'FROM dual;我只想展示
8 e$ h0 `  b% l. J& eapple此代码显示
with data as(  select 'apple,apple,apple,apple' col from dual)select listagg(col,',') within group(order by 1) col  from (       select distinct regexp_substr(col,'[^,] ',1,level) col          from data        connect by level 这样的事情会消除重复:
9 I( y% r4 G8 J) g; o: t: A, z4 eSQL演示
with temp as(    select 1 Name,'test1' Project,'apple,apple,orange' Error  from dual    union all    select 2,'test2','apple,apple,apple,apple,' from dual),split as  select distinct      t.name,t.project,     trim(regexp_substr(t.error,'[^,] ',1,levels.column_value))  as error    from       temp t,     table(cast(multiset(select level                           from dual connect by  level <= length (regexp_replace(t.error,'[^,] as sys.OdciNumberList)) levels)SELECT Name,listagg(Error,',') within group(order by 1) as result FROM splitGROUP BY Name