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;
Комментариев нет:
Отправить комментарий