AGGREGATE_STRING с задаваемым разделителем

  Когда-то возникла необходимость выводить в одну строку текстовые значения принадлежащие одной группе, все варианты решения данной задачи можно посмотреть здесь String Aggregation Techniques, на то время, два года назад, самым простым и практичным и понятным мне показался этот вариант User-Defined Aggregate Function, который я взял на вооружение. Остальные решения через курсор, COLLECT, появившуюся в Oracle 10g не применимы на большим объемах записей или ограничения на пользовательскую сессию, как в случае с open_cursors. А функция SYS_CONNECT_BY_PATH очень сложная для применения в контексте этой задачи.

  Через неделю меня не устроило что разделитель жестко задан константой (, - запятая), а мне понадобилось заюзать перевод каретки и я переписал агрегатную функцию, чтобы в качестве разделителя можно было передать любую строку длиной до 30-ти символов, для разделителя этого хватит с головой.
 
  Оказывается в агрегатную функцию, в качестве входящего параметра можно, вместо строкового типа, указать объект:

--CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
--RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING t_string_agg;

CREATE OR REPLACE FUNCTION AGGREGATE_STRING (input t_value_delimiter)
return varchar2 parallel_enable aggregate using t_aggregate_string;

Чтобы функция AGGREGATE_STRING скомпилировалась сначала надо создать объект T_VALUE_DELIMITER:

CREATE OR REPLACE TYPE T_VALUE_DELIMITER as object (
  value varchar2(4000),
  delimiter varchar2(30),
  order member function match(other t_value_delimiter) return number
);
/

CREATE OR REPLACE TYPE BODY T_VALUE_DELIMITER is
  order member function match(other t_value_delimiter)
    return number is
  begin
    if value < other.value then
      return -1;
    elsif value > other.value then
      return 1;
    else
      return 0;
    end if;
  end;
END;
/

GRANT EXECUTE ON T_VALUE_DELIMITER TO PUBLIC;

CREATE PUBLIC SYNONYM T_VALUE_DELIMITER FOR T_VALUE_DELIMITER;

  Свойство value задано 4000 (максимальная длина типа VARCHAR2, которая может возвращаться из PL/SQL процедур и функций). Будьте внимательны, если у вас база создана в юникоде, то агрегатная функция сможет вернуть не более двух тысяч символов.

CREATE OR REPLACE TYPE T_AGGREGATE_STRING as object (

    str_agg varchar2(4000),
    delimiter varchar2(30),

    static function ODCIAggregateInitialize(sctx  in out t_aggregate_string)
                    return number,

    member function ODCIAggregateIterate   (self  in out t_aggregate_string,
                                            value in t_value_delimiter)
                    return number,

    member function ODCIAggregateTerminate (self         in     t_aggregate_string,
                                            return_value    out varchar2,
                                            flags        in number      )
                    return number,

    member function ODCIAggregateMerge(self in out t_aggregate_string,
                                       ctx2 in t_aggregate_string    )
                    return number
);
/

CREATE OR REPLACE TYPE BODY         T_AGGREGATE_STRING is

    static function ODCIAggregateInitialize(sctx in out t_aggregate_string)
        return number is
    begin
        sctx := t_aggregate_string(null, null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(
      self in out t_aggregate_string, value in t_value_delimiter)
        return number is
    begin
        delimiter := value.delimiter;
        str_agg := str_agg || value.delimiter || value.value;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in t_aggregate_string,
        return_value out varchar2, flags in number) return number is
    begin
        return_value := LTRIM(str_agg, delimiter);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out t_aggregate_string,
        ctx2 in t_aggregate_string) return number is
    begin
        str_agg := str_agg || delimiter || ctx2.str_agg;
        return ODCIConst.Success;
    end;
end;
/

GRANT EXECUTE ON T_AGGREGATE_STRING TO PUBLIC;

-- Синоним не создаю так как пользователь к этому объекту явно
-- никогда не обратится
--CREATE PUBLIC SYNONYM T_AGGREGATE_STRING FOR T_AGGREGATE_STRING;

Наша агрегатная функция:

CREATE OR REPLACE FUNCTION AGGREGATE_STRING (input t_value_delimiter)
return varchar2 parallel_enable aggregate using t_aggregate_string;
/
GRANT EXECUTE ON AGGREGATE_STRING TO PUBLIC;

CREATE PUBLIC SYNONYM AGGREGATE_STRING FOR AGGREGATE_STRING;

Пример:

COLUMN value FORMAT A40

select gumanoid, aggregate_string(t_value_delimiter(feature, ', ')) gumanoid_group
from (
      select 'звери' gumanoid, 'собаки' feature from dual
      union all
      select 'птицы' gumanoid, 'синицы' feature from dual
      union all
      select 'звери' gumanoid, 'тюлени' feature from dual
      union all
      select 'птицы' gumanoid, 'воробьи' feature from dual
      union all
      select 'птицы' gumanoid, 'пеликаны' feature from dual
      union all
      select 'звери' gumanoid, 'медведи' feature from dual
     )
group by gumanoid;


GUMANOID VALUE                                   
-------- ----------------------------------------
звери    собаки, тюлени, медведи                 
птицы    синицы, пеликаны, воробьи               

2 rows selected.

Скрипт для удаления объектов

DROP PUBLIC SYNONYM T_VALUE_DELIMITER;
DROP PUBLIC SYNONYM AGGREGATE_STRING;
 
DROP FUNCTION AGGREGATE_STRING;
DROP TYPE BODY T_AGGREGATE_STRING;
DROP TYPE T_AGGREGATE_STRING;
DROP TYPE BODY T_VALUE_DELIMITER;
DROP TYPE T_VALUE_DELIMITER;

В Oracle 11g Release 2 появилась встроенная функция LISTAGG, которая делает агрегирование строк очень простым, плюс в ней, помимо разделителя, можно еще задавать сортировку в пределах группы:

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

А в Oracle 10g также подойдет вариант с XMLAGG:

WITH Q AS
(
      select 'звери' gumanoid, 'собаки' feature from dual
      union all
      select 'птицы' gumanoid, 'синицы' feature from dual
      union all
      select 'звери' gumanoid, 'тюлени' feature from dual
      union all
      select 'птицы' gumanoid, 'воробьи' feature from dual
      union all
      select 'птицы' gumanoid, 'пеликаны' feature from dual
      union all
      select 'звери' gumanoid, 'медведи' feature from dual
)
SELECT   gumanoid,
         RTRIM(
            XMLAGG (XMLELEMENT(e, feature||'; ') ORDER BY feature).EXTRACT('//text()'),
            '; '
         ) AS concatval
    FROM q
GROUP BY gumanoid;

GUMANOID CONCATVAL 
-------- ------------------------
звери    медведи; собаки; тюлени
птицы    воробьи; пеликаны; синицы
                                                                                
2 rows selected.

Но LISTAGG и XMLAGG не позволяет использовать distinct, поэтому ниже привожу пример использования XMLQUERY для удаления дублей из результата функций LISTAGG и XMLAGG:

SELECT LISTAGG (id, ',') WITHIN GROUP (ORDER BY id) listagg_no_distinct,
       TO_CHAR (
          RTRIM (
             XMLAGG (XMLELEMENT (e, id, ',').EXTRACT ('//text()') ORDER BY id).GetClobVal (),
             ','))
          xmlagg_no_distinct,
       XMLTYPE.getStringVal (
          XMLQUERY (
             (   'string-join(distinct-values(('''
              || REPLACE (LISTAGG (id, ',') WITHIN GROUP (ORDER BY id),
                          ',',
                          ''',''')
              || ''')), ",")')
             RETURNING CONTENT))
          listagg_distinct,
       XMLTYPE.getStringVal (
          XMLQUERY (
             (   'string-join(distinct-values(('''
              || REPLACE (
                    TO_CHAR (
                       RTRIM (
                          XMLAGG (XMLELEMENT (e, id, ',').EXTRACT (
                                     '//text()') ORDER BY id).GetClobVal (),
                          ',')),
                    ',',
                    ''',''')
              || ''')), ",")')
             RETURNING CONTENT))
          xmlagg_distinct
  FROM (SELECT ROWNUM rn, CASE WHEN LEVEL IN (3, 4, 7) THEN LEVEL END AS id
              FROM DUAL
        CONNECT BY LEVEL < 10
        UNION ALL
        SELECT 10, 7 FROM DUAL
       );