Недокумментированная функция Oracle WM_CONCAT

В Oracle можно встретить недокумментированные функции, которые свою очередь весьма полезны. Хотелось бы рассмотреть одну из них используемую и появившуюся в Oracle версиях 10g. В Oracle версиях выше 11g R2 есть уже докумментированная функция выполняющая тот же функционал. Наверное, интересно что это за функционал. Давайте рассмотрим стандартную задачу. У нас есть департамент и сотрудники соответственно прикрепленные за этим департаментом. Нам дали задачу вывести все департаменты их описание и имена сотрудников работающих в нем. При этом строк должно быть равно количеству департамента. Естественно у нас появляется вопрос, ведь сотрудники и департамент имеет соотношение один ко многим, плюс к тому же мы не сможем точно знать количество сотрудников в каждый момент, и заводить столбец для каждого сотрудника пустое расстачительство место, которое не имеет смысла. Одним из решение которое выглядит весьма красивым — есть перечисление имена всех сотрудников через запятую в одном столбце. Как же решить это используя только SQL не прибегая к программированию на PL/SQL.



Вот в этом случаи к нам на помощь и приходит WM_CONCAT, которая поможет соединить строки с наименьшими усилиями.

К примеру есть таблица по которой запрос ввиде

SELECT deptno, ename AS employees
FROM scott.emp

возвращает следующие строки:

DEPTNO EMPLOYEES

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

Мы хотим отобразить имена сотрудников через запятую согласно департамента в котором трудится этот сотрудник. Написав и выполнив запрос:

SELECT deptno, wm_concat (ename) AS employees
FROM scott.emp
GROUP BY deptno;

Получим

DEPTNO EMPLOYEES

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

Да впечатляет, легкость решение не простой задачи. Но здесь есть оговорки — Имена сотрудников можно перечислять только через запятую. Если количество сотрудников в одном департаменте составит больше определенного количество, то может вернутся ошибка. В некоторых релизах Oracle версиях 10g возможно данная функция будет работать иначе — это будет естественное поведение недокументированной функции, на то она и недокументированная.

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

 
SELECT deptno, LISTAGG(ename, ',') AS employees
FROM scott.emp
GROUP BY deptno;