четверг, 16 февраля 2017 г.

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;

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