STRING_AGG (Transact-SQL)
Область применения: 
SQL Server 2017 (14.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики SQL Azure Synapse Analytics в хранилище Microsoft Fabric в Microsoft Fabric
Сцепляет значения строковых выражений, помещая между ними значения-разделители. В конце строки разделитель не добавляется.
Соглашения о синтаксисе Transact-SQL
Синтаксис
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Аргументы
выражение
Выражение любого типа данных. Во время объединения выражения преобразуются в тип NVARCHAR или VARCHAR. Нестроковые типы преобразуются в тип NVARCHAR.
separator
Выражение типа NVARCHAR или VARCHAR, которое используется в качестве разделителя сцепляемых строк. Может быть литералом или переменной.
<order_clause>
При необходимости укажите очередность сцепляемых результатов с помощью предложения WITHIN GROUP:
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
<order_by_expression_list>
Список неконстантных выражений, который можно использовать для сортировки результатов. В запросе допускается только один аргумент order_by_expression. По умолчанию задан порядок сортировки по возрастанию.
Типы возвращаемых данных
Тип возвращаемого значения зависит от первого аргумента (выражения). Если входной аргумент имеет строковый тип (NVARCHAR, VARCHAR), результат будет иметь тот же тип. В приведенной ниже таблице перечислены автоматические преобразования.
| Тип входного выражения | Результат |
|---|---|
| NVARCHAR(MAX) | NVARCHAR(MAX) |
| VARCHAR(MAX) | VARCHAR(MAX) |
| NVARCHAR(1...4000) | NVARCHAR(4000) |
| VARCHAR(1...8000) | VARCHAR(8000) |
| int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime, datetime2, | NVARCHAR(4000) |
Замечания
STRING_AGG — это агрегатная функция, которая принимает все выражения из строк и сцепляет их в одну строку. Значения выражений неявно преобразуются в строковые типы и затем сцепляются. Неявное преобразование в строки выполняется по существующим правилам преобразования типов данных. Дополнительные сведения о преобразовании типов данных см. в статье Функции CAST и CONVERT (Transact-SQL).
Если входное выражение имеет тип VARCHAR, разделитель не может иметь тип NVARCHAR.
Значения NULL пропускаются, и соответствующий разделитель не добавляется. Чтобы вернуть заполнитель для значений NULL, используйте функцию ISNULL, как показано в примере Б.
Функция STRING_AGG доступна на любом уровне совместимости.
Примечание.
<order_clause> доступна с уровнем совместимости базы данных 110 и выше.
Примеры
Большинство примеров в этой статье ссылаются на образцы баз данных AdventureWorks.
А. Формирование списка имен, разделенного по строкам
В приведенном ниже примере формируется список имен в одной результирующей ячейке, разделенный символами возврата каретки.
USE AdventureWorks2022;
GO
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), CHAR(13)) AS csv
FROM Person.Person;
GO
Вот результирующий набор.
| csv |
|---|
| Саид Екатерининский Ким Ким Ким Хем ... |
Значения NULL, найденные в ячейках name, не возвращаются в результатах.
Примечание.
Если используется Редактор запросов SQL Server Management Studio, параметр "Результаты в сетку" не может реализовать возврат каретки. Чтобы результирующий набор отображался правильно, перейдите в режим В виде текста.
По умолчанию результаты в виде текста усекаются до 256 символов. Чтобы увеличить это ограничение, измените значение параметра Максимальное число символов, отображаемых в каждом столбце.
B. Формирование списка имен, разделенного запятыми, без значений NULL
В приведенном ниже примере значения NULL заменяются на "N/A" и имена, разделенные запятыми, возвращаются в одной результирующей ячейке.
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(FirstName,'N/A')), ',') AS csv
FROM Person.Person;
GO
Вот результирующий набор.
Примечание.
Результирующий набор отображается обрезанным.
| csv |
|---|
| Syed,Catherine,Kim,Kim,Kim,Hazem,Sam,Humberto,Gustavo,Pilar,Pilar, ... |
В. Формирование списка значений с разделителями-запятыми
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names
FROM Person.Person;
GO
Вот результирующий набор.
Примечание.
Результирующий набор отображается обрезанным.
| Имена |
|---|
| Кен Санчес (8 февраля 2003 г. 12:00 УТРА) Терри Даффи (24 февраля 2002 12:00 УТРА) Роберто Тамбурелло (5 декабря 2001 г. 12:00 УТРА) Роб Уолтерс (29 декабря 2001 г. 12:00 УТРА) ... |
Примечание.
Если в редакторе запросов Management Studio используется режим В виде сетки, символы возврата каретки не применяются. Чтобы результирующий набор отображался правильно, перейдите в режим В виде текста.
D. Получение новых статей со связанными тегами
Представьте базу данных, в которой статьи и их теги разделены по разным таблицам. Разработчику необходимо вернуть одну строку для каждой статьи со всеми связанными тегами. Этот результат имеет следующий запрос:
SELECT a.articleId, title, STRING_AGG (tag, ',') as tags
FROM dbo.Article AS a
LEFT JOIN dbo.ArticleTag AS t
ON a.ArticleId = t.ArticleId
GROUP BY a.articleId, title;
GO
Вот результирующий набор.
| articleId | title | tags |
|---|---|---|
| 172 | Опросы предвещают напряженную борьбу на выборах | политика,опросы,муниципалитет |
| 176 | Новая автострада разгрузит транспортные потоки | NULL |
| 177 | Собаки по-прежнему популярнее кошек | опросы,животные |
Примечание.
Предложение GROUP BY является обязательным, если функция STRING_AGG не является единственным элементом в списке SELECT.
Е. Формирование списка адресов электронной почты по городам
Следующий запрос находит адреса электронной почты сотрудников и группирует их по городам:
USE AdventureWorks2022;
GO
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') AS emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
GO
Вот результирующий набор.
Примечание.
Результирующий набор отображается обрезанным.
| Город | сообщений электронной почты |
|---|---|
| Ballard | paige28@adventure-works.com;joshua24@adventure-works.com;javier12@adventure-works.com; ... |
| Балтимор | gilbert9@adventure-works.com |
| Барстоу | kristen4@adventure-works.com |
| Basingstoke Hants | dale10@adventure-works.com;heidi9@adventure-works.com |
| Baytown | kelvin15@adventure-works.com |
| Бивертон | billy6@adventure-works.com;dalton35@adventure-works.com;lawrence1@adventure-works.com; ... |
| Bell Gardens | christy8@adventure-works.com |
| Bellevue | min0@adventure-works.com;gigi0@adventure-works.com;terry18@adventure-works.com; ... |
| Bellflower | philip0@adventure-works.com;emma34@adventure-works.com;jorge8@adventure-works.com; ... |
| Bellingham | christopher23@adventure-works.com;frederick7@adventure-works.com;omar0@adventure-works.com; ... |
Адреса, возвращенные в столбце emails, можно использовать для рассылки сообщений группе людей, работающих в определенном городе.
F. Формирование отсортированного списка адресов электронной почты по городам
Так же как в предыдущем примере, следующий запрос находит адреса электронной почты сотрудников, группирует их по городам и сортирует по алфавиту:
USE AdventureWorks2022;
GO
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') WITHIN GROUP (ORDER BY EmailAddress ASC) AS Emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
GO
Вот результирующий набор.
Примечание.
Результирующий набор отображается обрезанным.
| Город | Сообщения электронной почты |
|---|---|
| Барстоу | kristen4@adventure-works.com |
| Basingstoke Hants | dale10@adventure-works.com;heidi9@adventure-works.com |
| Braintree | mindy20@adventure-works.com |
| Bell Gardens | christy8@adventure-works.com |
| Byron | louis37@adventure-works.com |
| Bordeaux | ranjit0@adventure-works.com |
| Carnation | don0@adventure-works.com;douglas0@adventure-works.com;george0@adventure-works.com; ... |
| Boulogne-Billancourt | allen12@adventure-works.com;bethany15@adventure-works.com;carl5@adventure-works.com; ... |
| Berkshire | barbara41@adventure-works.com;brenda4@adventure-works.com;carrie14@adventure-works.com; ... |
| Berks | adriana6@adventure-works.com;alisha13@adventure-works.com;arthur19@adventure-works.com; ... |
Следующие шаги
Дополнительные сведения о функциях Transact-SQL см. в следующих статьях: