sql несколько строк в одно поле
+= (присваивание объединения строк) (Transact-SQL)
Объединяет две строки и присваивает строке результат этой операции. Например, если переменная @x имеет значение ‘Adventure’, то операция @x += ‘Works’ принимает исходное значение @x, добавляет к нему строку ‘Works’ и присваивает переменной @x новое значение (‘AdventureWorks’).
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
expression
Любое допустимое выражение любого символьного типа данных.
Типы результата
Возвращает тип данных, определенный для переменной.
Remarks
SET @v1 += ‘expression’ эквивалентно SET @v1 = @v1 + ‘expression’. Кроме того, SET @v1 = @v2 + @v3 + @v4 эквивалентно SET @v1 = (@v2 + @v3) + @v4.
Оператор += нельзя использовать без переменной. Например, следующий код вызывает ошибку:
Примеры
A. Объединение с использованием оператора +=
This is the original. More text.
Б. Порядок вычисления во время объединения с помощью оператора +=
В приведенном ниже примере выполняется объединение нескольких строк в одну длинную строку, а затем предпринимается попытка вычислить длину итоговой строки. В этом примере демонстрируются правила оценки и усечения при использовании оператора объединения.
Как объединить текст из нескольких строк в одну текстовую строку в SQL server?
Рассмотрим таблицу базы данных, содержащую имена, с тремя строками:
30 ответов
если вы находитесь в SQL Server 2017 или Azure, см. Матье Ренда ответ.
у меня была аналогичная проблема, когда я пытался объединить две таблицы с отношениями один ко многим. В SQL 2005 я обнаружил, что XML PATH метод может обрабатывать конкатенацию строк очень легко.
если есть таблица под названием STUDENTS
результат, который я ожидал, был:
я использовал следующие T-SQL :
вы можете сделайте то же самое более компактным способом, если вы можете объединить запятые в начале и использовать substring чтобы пропустить первый, поэтому вам не нужно делать подзапрос:
этот ответ может возвращать неожиданные результаты при наличии предложения ORDER BY. Для получения согласованных результатов используйте один из методов пути for XML, описанных в других ответах.
просто некоторое объяснение (так как этот ответ, похоже, получает относительно регулярные представления):
1) Нет необходимости инициализировать @Names С пустой строкой.
2) отсутствие потребности обнажать дополнительный разделитель в конце.
в зависимости от того, какое поведение вы хотите (первый вариант просто фильтров NULLs out, второй вариант сохраняет их в списке с сообщением маркера [заменить ‘N / A’ на то, что подходит для вас]).
один метод еще не показан через XML data() команда в MS SQL Server:
предположим, что таблица называется NameList с одним столбцом с именем FName,
необходимо иметь дело только с дополнительной запятой.
Edit: как принято из комментария @NReilingh, вы можете использовать следующий метод для удаления конечной запятой. Предполагая те же имена таблиц и столбцов:
+ (объединение строк) (Transact-SQL)
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
expression
Любое действительное выражение любого типа данных в категории символьных и двоичных данных, за исключением типов данных image, ntext и text. Оба выражения должны иметь одинаковый тип данных, или одно из выражений должно допускать неявное преобразование к типу данных другого выражения.
При сцеплении двоичных строк с любыми символами между двоичными строками необходимо использовать явное преобразование в символьные данные. В следующем примере показано, когда CONVERT или CAST должны использоваться с двоичной конкатенацией и когда CONVERT или CAST не нужно использовать.
Типы результата
Возвращает тип данных аргумента с самым высоким приоритетом. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).
Комментарии
При работе с пустыми строками нулевой длины оператор + (объединение строк) ведет себя иначе, чем при работе со значениями NULL или с неизвестными значениями. Символьная строка символа нулевой длины может быть указана в виде двух одинарных кавычек без каких-либо символов между ними. Двоичная строка нулевой длины может быть указана как 0x без указания каких-либо байтовых значений в шестнадцатеричной константе. При сцеплении строки нулевой длины всегда сцепляются две указанные строки. При работе со строками со значением NULL результат объединения зависит от настроек сеанса. При присоединении нулевого значения к известному значению результатом будет неизвестное значение, объединение строк с нулевым значением также дает нулевое значение, как и в арифметических действиях с нулевыми значениями. Однако можно изменить данное поведение, поменяв значение CONCAT_NULL_YIELDS_NULL для текущего сеанса. Дополнительные сведения см. в разделе SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).
Если результат объединения строк превышает предел в 8 000 байт, то он усекается. Однако усечения не произойдет, если хотя бы одна из сцепляемых строк принадлежит к типу больших значений.
Примеры
A. Использование объединения строк
В следующем примере создается единственный столбец с заголовком Name из нескольких символьных столбцов, где за фамилией лица следуют запятая, один пробел и имя того же лица. Результирующий набор сортируется в алфавитном порядке по возрастанию, сначала по фамилии, а затем по имени.
Б. Объединение числовых типов данных и дат
В приведенном ниже примере функция CONVERT используется для объединения типов данных numeric и date.
В. Использование объединения нескольких строк
Следующий пример сцепляет несколько строк в одну длинную строку для отображения фамилии и первой буквы инициалов вице-президентов в Компания Adventure Works Cycles. После фамилии ставится запятая, а после первой буквы инициалов — точка.
Г. Использование больших строк при объединении
В приведенном ниже примере выполняется объединение нескольких строк в одну длинную строку, а затем предпринимается попытка вычислить длину итоговой строки. Итоговая длина результирующего набора равна 16 000, так как вычисление выражения начинается слева, то есть @x + @z + @y => (@x + @z) + @y. В этом случае результат (@x + @z) усекается до 8000 байт, а затем в результирующий набор добавляется значение @y, из-за чего длина итоговой строки становится равна 16 000. Так как @y — это строка типа с большим значением, усечения не происходит.
Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)
Д. Использование объединения нескольких строк
В приведенном ниже примере несколько строк сцепляются в одну длинную строку для отображения фамилий и инициалов имен вице-президентов из образца базы данных. После фамилии ставится запятая, а после первой буквы инициалов — точка.
5 cпособов осуществить агрегацию строк в MS SQL
Иногда возникает необходимость осуществить агрегацию строк в SQL запросе, то есть, по такому набору данных:
| GroupId | Item |
|---|---|
| 1 | AAA |
| 2 | IS |
| 5 | OMG |
| 2 | WHAT |
| 2 | THE |
| 1 | This |
получить примерно такой:
| GroupId | ItemList |
|---|---|
| 1 | AAA,This |
| 2 | IS,WHAT,THE |
| 5 | OMG |
MySQL, например, для таких целей обладает встроенной функцией GROUP_CONCAT():
Да, много кода. Но зато ни одного лишнего объекта в БД — просто один чистый селект. Это иногда важно.
Тем не менее, существует способ обойти и ограничение на размер группы, оставшись при этом в рамках одного запроса. Мы будем собирать все элементы группы в XML-поле, которое затем сконвертим к строковому типу и заменим теги между элементами на запятые:
В общем, работает не очень шустро, зато всегда. И, конечно, нужен SQL Server не ниже 2000.
Да, еще есть способ агрегировать строки через CLR Aggregate Functions, но это вообще мрачный ужас, ибо вусмерть медленно и несообразно сложности задачи. Если возникнет достаточный спрос на такую статью, напишу её позже.
Жду с нетерпением комментариев и критики. И ещё: если кто-нибудь знает, как можно сделать такую подсветку кода, как я сделал у себя дома — подскажите. Я, кроме как вставить скриншоты, другого способа пока не вижу.
Соединение строк SQL
В процессе работы я столкнулся с необходимостью объединения строк в базе данных. Казалось бы, это достаточно тривиальная задача, но не все так просто, как кажется на первый взгляд. Давайте подробнее рассмотрим различные методы конкатенации строк в SQL и те проблемы, с которыми я столкнулся.
Оператор +
Наиболее простым методом объединения строк является использование обычного оператора +. При этом к первому аргументу будет добавлен второй. Например если мы возьмем слово «Чудо» и слово «Женщина» в результате получим строку «ЧудоЖенщина».
Пока все просто, но это не то, что было мне нужно. Потому что, мне было нужно объединять большое количество значений, а делать это через знак + было не удобно. Давайте рассмотрим и другие способы конкатенации.
Функция CONCAT
Данная функция позволяет объединить все параметры, передаваемые в нее в качестве аргументов. Минимальное количество параметров два. Добавлю очень важное замечание, данная функция появилась в SQL Server начиная с 2012 версии. Это, кстати, принципиально. Потому что зачастую используются старые версии сервера, и вы не сможете использовать новые возможности языка. Всегда проверяйте, поддерживает ли ваша версия SQL сервера используемые возможности. Особенно при переносе с одного сервера на другой.
Работает данная функция аналогично оператору +. Например:
Но этот вариант меня тоже не устраивал, потому что мне нужно было объединить результаты через запятую.
Функция CONCAT_WS
Данная функция позволяет конкатенировать строки через разделитель. Но ее основной проблемой, как и многих других, которые мы рассмотрим в дальнейшем, что она была добавлена только в версии SQL Server 2017, который пока практически нигде не используется. Первым параметром указывается разделитель, затем перечисляются параметры.
Так как у меня используется SQL куда более старой версии, то воспользоваться я ей благополучно не смог… Поэтому пришлось начать куда более не стандартные функции для конкатенации.
Функция STAFF
Переходим к более сложным примерам, и для начала рассмотрим предметную область. У нас есть три таблицы, это Человек, Проект и Назначение. Все достаточно просто, обычная реализация связи многие к многим человека и проекта.
Если очень упростить, то задача состояла в том, чтобы вывести через запятую все проекты назначенные на человека. Сначала я попытался воспользоваться функцией STAFF. Рассмотрим элементарный, но бесполезный пример
Здесь, в подзапросе мы получаем все элементы таблицы Project, и соединяем их через точку с запятой, начиная именно с точки запятой, а затем просто удаляем первый символ (как раз не нужную точку с запятой). И выводим для каждого назначения.
Но когда я захотел сгруппировать результаты по идентификатору пользователя с помощью GROUP BY я узнал, что сделать этого не смогу, так как STAFF не является агрегирующей функцией. Поэтому пошел дальше изучать просторы интернета.
Функция STRING_AGG
Отличная функция которая полностью решила все мои проблемы. За исключением одного, она появилась в SQL Server 2017, поэтому оказалась хоть и очень хорошей, но бесполезной
Функция GROUP_CONCAT
Ну и наконец самое сладкое. Не найдя ни одной подходящей конкатенирующей агрегирующей функции, я нашел возможность с помощью кастомной агрерирующей функции. Есть проект на github orlando-colamatteo/ms-sql-server-group-concat-sqlclr, который предоставляет готовый скрипт, который добавляет новую конкатенирующую функцию GROUP_CONCAT. Посмотрим пример.
Именно благодаря этой функции я решил все свои проблемы и выполнил поставленную задачу. Моя огромная благодарность ее создателю. Ну а теперь давайте рассмотрим процесс установки и что это вообще такое.
Установка GROUP_CONCAT в MS SQL Server
Для начала заходим на github и скачиваем проект. Распаковываем его в любую директорию. Заходим в папку D:\ms-sql-server-group-concat-sqlclr-master\GroupConcat\Installation Scripts и открываем файл GroupConcatInstallation.sql. Он уже практически готов к использованию. Единственно что нужно сделать, это изменить имя базы данных на используемое у вас.
Обратите внимание, что для выполнения вам потребуются права администратора на SQL Server. После этого вы сможете использовать данную агрегатную функцию в пределах базы данных. MS SQL Management Studio может подчеркивать функцию как ошибку, но не пугайтесь, он будет успешно работать.
Что такое SQL CLR?
С ее помощью предоставляется возможность создания пользовательских функций, типов данных, аргегатных функций, триггеров, хранимых процедур, написанных на высокопроизводительных языках. Благодаря этому можно значительно увеличить производительность, а также расширять функционал стандартного SQL. Пример можно изучить в статье на Habr.
Соединение строк sql — Заключение
На этом у меня все. Надеюсь данный материал будет полезен. Но больше всего меня поражает то, что компания Microsoft ввела агрегирующую функцию конкатенации строк только в 2017 году…
Кроме того, рекомендую прочитать статью Работа с XML на языке C#. А также подписывайтесь на группу ВКонтакте, Telegram и YouTube-канал. Там еще больше полезного и интересного для программистов.