2017-02-16

LISTAGG - remove duplicates

My colleague Victor help me to find solution for task:
Aggregate string from query result without duplicates.
Almost all solutions, that I find in internet was like
SELECT LISTAGG(str, ',') WITHIN GROUP (ORDER BY 1)
FROM (SELECT DISTINCT str FROM tab);
But if you have scalar subquery with filter condition this solution doesn’t work because of 2-levels of nesting.
Below there are 2 solutions with regexps and xslt -transformations
SELECT (
   SELECT regexp_replace(LISTAGG(object_type, ',') WITHIN GROUP (ORDER BY object_type), '([^,]+)(,\1)+', '\1') 
   FROM user_objects
   ) solution1,
   (
   SELECT rtrim(xmltype('<r><n>' || LISTAGG(object_type, ',</n><n>') WITHIN GROUP (ORDER BY object_type) || ',</n></r>').extract('//n[not(preceding::n = .)]/text()').getstringval(), ',')
   FROM user_objects
   ) solution2
FROM dual;
UPD: Starting from Oracle 19c this scripts are not relevant: we can use native syntax:
SELECT listagg(distinct object_type, ', ') txt FROM all_objects;

Комментариев нет: