О соединении данных столбца в одну строку с помощью SQL-запроса

Метки: ,

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

Часть I. Аналитика и иерархия.

Простой способ решения задачи подсказал Т.Кайт в статье On Ignoring, Locking, and Parsing. Этот способ основывается на использовании аналитических функций и функции SYS_CONNECT_BY_PATH для иерархических запросов и применим начиная с Oracle9i Database Release 1. Идея заключается в следующем.

  1. Выбираемые данные необходимо разделить на подгруппы, для которых будут склеиваться строки и в каждой подгруппе пронумеровать элементы в порядке их склеивания. 
    select
    deptno,
    ename,
    row_number() over (partition by deptno order by ename) rn
from emp

     

  2. С помощью иерархического запроса все данные подгруппы соединяются в одну ветку дерева. Для каждого узла с помощью функции SYS_CONNECT_BY_PATH вычисляется строка со всеми предшествующими значениями поля, разделенными заданным разделителем. 
    select
    deptno,
    sys_connect_by_path(ename, ' ' ) scbp
from
    (
        select
            deptno,
            ename,
            row_number() over (partition by deptno order by ename) rn
        from emp
    )
start with rn = 1
connect by prior rn = rn-1
and prior deptno = deptno

     

  3. Далее остается сгруппировать полученные данные по идентификатору подгруппы и выбрать для каждой значение строки максимальной длинны. 
    select
    deptno,
    max(sys_connect_by_path(ename, ' ' )) scbp
from
    (
        select
            deptno,
            ename,
            row_number() over (partition by deptno order by ename) rn
        from emp
    )
start with rn = 1
connect by 
    prior rn = rn-1
    and prior deptno = deptno
group by deptno
order by deptno

     

В результате получаем список сотрудников по отделам.

DEPTNO SCBP
--------- ----------------------------------
10 CLARK KING MILLER
20 ADAMS FORD JONES SCOTT ...
30 ALLEN BLAKE JAMES MARTIN ...

 

Этот способ прост и удобен, но имеет один недостаток. Функция SYS_CONNECT_BY_PATH работает с переменными VARCHAR2, которые, как известно, ограничиваются размером 4000 символов. При большом объеме склеиваемых данных, неизбежно возникает ошибка ORA-01489: result of string concatenation is too long. Эту проблему решает второй способ решения задачи.

Часть II. XML и CLOB.

Второй вариант предусматривает использование аггрегатной функции XMLAGG для получения склеенной строки в виде XML-объекта, который может быть преобразован в CLOB.

select
    deptno,
    XMLAGG(XMLELEMENT("node", ename)).getCLOBVal() scbp
from emp
group by deptno

 

Для упорядочивания элементов в склеиваемой строке функции XMLAGG можно добавить сортировку.

select
    deptno,
    XMLAGG(XMLELEMENT("node", ename) order by ename).getCLOBVal() scbp
from emp
group by deptno

 

А если возникнет необходимость отсортировать полученные склеенные строки, можно воспользоваться преобразованием типов cast.

select
    deptno,
    XMLAGG(XMLELEMENT("node", ename) order by ename).getCLOBVal() scbp
from emp
group by deptno
order by cast( substr(scbp, 1, 4000) as varchar2(4000))

 

В результате получится следующее:

DEPTNO SCBP
--------- ----------------------------------
20 ADAMSFORD ...
30 ALLENBLAKE ...
10 CLARKKING

 

Недостаток данного способа – наличие тэгов, которые необходимо обрабатывать в зависимости от целей поставленной задачи. Хотя для случая, ставшего причиной поиска описанного решения, тэги оказались скорее плюсом.

Этот же метод можно применить и для более коротких строк, где не требуется использование CLOB-данных. XML-объект можно преобразовать в varchar2 с помощью функции getStringVal().

select
    deptno,
    XMLAGG(XMLELEMENT("node", ename) order by ename).getStringVal() scbp
from emp
group by deptno
order by scbp

 

Close Menu