Конкатенация строк в T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Конкатенация строк

Как уже говорилось, в стандарте SQL определены только два типа функций упорядоченного набора: функции гипотетического набора (RANK, DENSE_RANK, PERCENT_RANK и CUME_DIST) и функции обратного распределения (PERCENTILE_DISC и PERCENTILE_CONT). Как я уже демонстрировал на примере функций сдвига, нет причины, по которой эта концепция не работала бы и для других функций. Основная идея состоит в том, что если это агрегирующая функция, результат вычислении которой зависит от порядка следования элементов, это возможный кандидат на функцию упорядоченного набора.

Возьмем такой классический пример как конкатенация строки. К сожалению, на настоящий момент не существует встроенной агрегирующей функции конкатенации строки, которая бы соединяла группу строк. Но допустим, что такая функция существует. Конечно же, у вас может возникнуть необходимость в конкатенации группы строк в некотором порядке, поэтому имеет смысл реализовать такую функцию как функцию упорядоченного набора с предложением WITHIN GROUP, которое позволяет задать параметры упорядочения.

В Oracle, например, такая функция реализована (называется LISTAGG) как функция упорядоченного набора. Итак, чтобы обратиться к таблице с именем Sales.Orders и вернуть для каждого клиента строку со значениями orderid конкатенированными в порядке orderid, используйте следующий код:

-- Для Oracle
SELECT custid,
  LISTAGG(orderid, ',') WITHIN GROUP(ORDER BY orderid) AS custorders
FROM Sales.Orders
GROUP BY custid;

В SQL Server разработчики прибегают к самым разным альтернативным решениям, чтобы получить конкатенацию строк в определенном порядке. Один из наиболее эффективных приемов основывается на обработке XML с использованием параметра FOR XML в режиме PATH, примерно так:

SELECT custid,
  COALESCE(
    STUFF(
      (SELECT ',' + CAST(orderid AS VARCHAR(10)) AS [text()]
       FROM Sales.Orders AS O
       WHERE O.custid = C.custid
       ORDER BY orderid
       FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
      1, 1, ''),
    '') AS custorders
FROM Sales.Customers AS C;

Расположенный на самом нижнем уровне вложения связанный вложенный запрос отфильтровывает только значения orderid из таблицы Orders (псевдоним O), которые связаны с текущим клиентом из таблицы Customers (псевдоним C). Используя предложение FOR XML PATH, можно объединить все значения одну строку XML. Использование пустой строки как входного значения в режиме PATH означает, что инкапсулирующие элементы не нужны, поэтому мы получаем конкатенацию значений без всяких тегов. Так как вложенный запрос содержит ORDER BY orderid, значения orderid в строке будут упорядочены. Заметьте, что упорядочивать можно по любому признаку — не обязательно по значениям, которые конкатенируются. Приведенный код также добавляет запятую в качестве разделителя перед каждым значением orderid, а затем функция STUFF удаляет первую запятую. И наконец, функция COALESCE преобразует результат NULL в пустую строку. Итак, мы видим, что существует возможность получить в SQL Server конкатенацию строк в определенном порядке, но выглядит это не очень изящно.

Итак, функции упорядоченного набора, которые мы рассмотрели ранее, это агрегирующие функции, результат вычисления которых зависит от упорядочения. В стандарте определено несколько специализированных функций, но принцип является общим и может применяться ко всем видам вычислений агрегатов. Я привел несколько примеров, выходящих за пределы поддерживаемого стандарта, — это функции смещения и конкатенация строк. SQL Server 2012 не поддерживает функции упорядоченного набора данных, но я привел альтернативные методы для получения аналогичной функциональности. Я очень надеюсь, что в будущем мы увидим в SQL Server поддержку таких функций — возможно, они будут реализовывать стандартное предложение WITHIN GROUP и будут доступны через пользовательские CLR-функции агрегирования, учитывающие упорядочение.