В процессе работы я столкнулся с необходимостью объединения строк в базе данных. Казалось бы, это достаточно тривиальная задача, но не все так просто, как кажется на первый взгляд. Давайте подробнее рассмотрим различные методы конкатенации строк в SQL и те проблемы, с которыми я столкнулся.
Оператор +
Наиболее простым методом объединения строк является использование обычного оператора +. При этом к первому аргументу будет добавлен второй. Например если мы возьмем слово «Чудо» и слово «Женщина» в результате получим строку «ЧудоЖенщина».
SELECT 'чудо' + 'женщина' AS Result
Пока все просто, но это не то, что было мне нужно. Потому что, мне было нужно объединять большое количество значений, а делать это через знак + было не удобно. Давайте рассмотрим и другие способы конкатенации.
Функция CONCAT
Данная функция позволяет объединить все параметры, передаваемые в нее в качестве аргументов. Минимальное количество параметров два. Добавлю очень важное замечание, данная функция появилась в SQL Server начиная с 2012 версии. Это, кстати, принципиально. Потому что зачастую используются старые версии сервера, и вы не сможете использовать новые возможности языка. Всегда проверяйте, поддерживает ли ваша версия SQL сервера используемые возможности. Особенно при переносе с одного сервера на другой.
Работает данная функция аналогично оператору +. Например:
SELECT CONCAT('чудо','женщина') AS Result
Но этот вариант меня тоже не устраивал, потому что мне нужно было объединить результаты через запятую.
Функция CONCAT_WS
Данная функция позволяет конкатенировать строки через разделитель. Но ее основной проблемой, как и многих других, которые мы рассмотрим в дальнейшем, что она была добавлена только в версии SQL Server 2017, который пока практически нигде не используется. Первым параметром указывается разделитель, затем перечисляются параметры.
SELECT CONCAT_WS(' ', 'чудо','женщина') AS Result
Так как у меня используется SQL куда более старой версии, то воспользоваться я ей благополучно не смог… Поэтому пришлось начать куда более не стандартные функции для конкатенации.
Функция STAFF
Переходим к более сложным примерам, и для начала рассмотрим предметную область. У нас есть три таблицы, это Человек, Проект и Назначение. Все достаточно просто, обычная реализация связи многие к многим человека и проекта.
Если очень упростить, то задача состояла в том, чтобы вывести через запятую все проекты назначенные на человека. Сначала я попытался воспользоваться функцией STAFF. Рассмотрим элементарный, но бесполезный пример
SELECT
STUFF(
(SELECT ';' + proj.Name
FROM Project AS proj
FOR XML PATH (''))
, 1, 1, '') AS Projects
FROM Assignment AS assign
Здесь, в подзапросе мы получаем все элементы таблицы Project, и соединяем их через точку с запятой, начиная именно с точки запятой, а затем просто удаляем первый символ (как раз не нужную точку с запятой). И выводим для каждого назначения.
Но когда я захотел сгруппировать результаты по идентификатору пользователя с помощью GROUP BY я узнал, что сделать этого не смогу, так как STAFF не является агрегирующей функцией. Поэтому пошел дальше изучать просторы интернета.
Функция STRING_AGG
Отличная функция которая полностью решила все мои проблемы. За исключением одного, она появилась в SQL Server 2017, поэтому оказалась хоть и очень хорошей, но бесполезной
SELECT assign.PersonId,
STRING_AGG (proj.Name, ',') AS Projects
FROM Assignment AS assign
LEFT JOIN Project AS proj ON assign.ProjectId = proj.Id
GROUP BY assign.PersonId
Функция GROUP_CONCAT
Ну и наконец самое сладкое. Не найдя ни одной подходящей конкатенирующей агрегирующей функции, я нашел возможность с помощью кастомной агрерирующей функции. Есть проект на github orlando-colamatteo/ms-sql-server-group-concat-sqlclr, который предоставляет готовый скрипт, который добавляет новую конкатенирующую функцию GROUP_CONCAT. Посмотрим пример.
SELECT assign.PersonId,
dbo.GROUP_CONCAT(proj.Name) AS Projects
FROM Assignment AS assign
LEFT JOIN Project AS proj ON assign.ProjectId = proj.Id
GROUP BY assign.PersonId
Именно благодаря этой функции я решил все свои проблемы и выполнил поставленную задачу. Моя огромная благодарность ее создателю. Ну а теперь давайте рассмотрим процесс установки и что это вообще такое.
Установка GROUP_CONCAT в MS SQL Server
Для начала заходим на github и скачиваем проект. Распаковываем его в любую директорию. Заходим в папку D:\ms-sql-server-group-concat-sqlclr-master\GroupConcat\Installation Scripts и открываем файл GroupConcatInstallation.sql. Он уже практически готов к использованию. Единственно что нужно сделать, это изменить имя базы данных на используемое у вас.
-- !! MODIFY TO SUIT YOUR TEST ENVIRONMENT !!
USE GroupConcatTest /*МЕНЯТЬ ЗДЕСЬ!*/
Обратите внимание, что для выполнения вам потребуются права администратора на SQL Server. После этого вы сможете использовать данную агрегатную функцию в пределах базы данных. MS SQL Management Studio может подчеркивать функцию как ошибку, но не пугайтесь, он будет успешно работать.
Что такое SQL CLR?
SQL CLR это технология компании Microsoft, которая позволяет добавлять новый функционал в MS SQL Server 2005 и более поздних версий при помощи внедрения сборок написанных с помощью языков входящих в .NET, такие как VB.NET или C#.
С ее помощью предоставляется возможность создания пользовательских функций, типов данных, аргегатных функций, триггеров, хранимых процедур, написанных на высокопроизводительных языках. Благодаря этому можно значительно увеличить производительность, а также расширять функционал стандартного SQL. Пример можно изучить в статье на Habr.
Заключение
На этом у меня все. Надеюсь данный материал будет полезен. Но больше всего меня поражает то, что компания Microsoft ввела агрегирующую функцию конкатенации строк только в 2017 году…
Источник: CODE BLOG