STRING AGG: примеры (SQL)

Использование STRING_AGG для конкатенации строк в SQL Server
Раздел: Строковые функции, Агрегатные
STRING_AGG(expression, separator): string
типовые примерыSTRING AGG на mySQL, Oracle, PostgreSQL, SQLiteSTRING AGG изменения sqlпримеры расширенные

Функция STRING_AGG в MS SQL Server

Функция STRING_AGG выполняет конкатенацию строковых значений из группы строк с указанным разделителем. Она появилась в SQL Server 2017 и служит для замены более сложных конструкций с FOR XML PATH. Функция применяется, когда необходимо собрать значения из нескольких строк в одну результирующую строку, например, для формирования списка через запятую.

Синтаксис: STRING_AGG ( expression, separator ) [ <order_clause> ]

Аргументы:

  • expression: выражение любого типа, которое неявно преобразуется в VARCHAR или NVARCHAR. Не может быть агрегатной функцией или подзапросом.
  • separator: константа или переменная типа VARCHAR или NVARCHAR, используемая в качестве разделителя между значениями. Может быть пустой строкой.
  • <order_clause>: необязательное предложение ORDER BY { expression | column_position } [ ASC | DESC ] [, ...n ], определяющее порядок соединения значений. Предложение указывается внутри агрегатной функции.

Возвращаемое значение: результат имеет тип VARCHAR(MAX), если expression преобразуется в VARCHAR, иначе NVARCHAR(MAX). Если входные строки содержат значение NULL, оно игнорируется и не добавляется в результат. При отсутствии строк для агрегации функция возвращает NULL.

Основные примеры применения

Простое объединение значений через запятую.

SELECT STRING_AGG(ProductName, ', ') AS ProductList
FROM Products
WHERE CategoryID = 1;
ProductList
-----------------------------
Product A, Product B, Product C

Использование с предложением ORDER BY внутри функции.

SELECT STRING_AGG(ProductName, ', ') WITHIN GROUP (ORDER BY Price DESC) AS SortedList
FROM Products
WHERE CategoryID = 1;
SortedList
-----------------------------
Product C, Product B, Product A

Применение пустого разделителя и обработка NULL.

SELECT STRING_AGG(CAST(ProductID AS VARCHAR), '') AS ConcatenatedIDs
FROM Products
WHERE CategoryID = 1;
ConcatenatedIDs
---------------
123

Похожие функции в MS SQL

До появления STRING_AGG часто использовался метод FOR XML PATH для конкатенации строк. Он более многословный, но может быть полезен в сложных сценариях, например, когда требуется особая обработка XML-символов.

Пример с FOR XML PATH:

SELECT STUFF((
    SELECT ', ' + ProductName
    FROM Products
    WHERE CategoryID = 1
    ORDER BY Price DESC
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ProductList;

Функция STRING_AGG проще в использовании и читаемости для базовых задач конкатенации. FOR XML PATH остается предпочтительным, если нужна сложная логика форматирования или обработка специальных символов.

Аналоги в других СУБД и языках

MySQL: функция GROUP_CONCAT. Похожа на STRING_AGG, но имеет дополнительные опции, такие как DISTINCT и SEPARATOR.

SELECT GROUP_CONCAT(ProductName SEPARATOR ', ') AS ProductList
FROM Products
WHERE CategoryID = 1;

PostgreSQL: функция STRING_AGG имеет аналогичный синтаксис и поведение.

SELECT STRING_AGG(ProductName, ', ' ORDER BY Price DESC)
FROM Products
WHERE CategoryID = 1;

Oracle: функция LISTAGG. Поддерживает WITHIN GROUP для сортировки, но может вызывать ошибку при превышении длины строки.

SELECT LISTAGG(ProductName, ', ') WITHIN GROUP (ORDER BY Price DESC)
FROM Products
WHERE CategoryID = 1;

SQLite: функция GROUP_CONCAT. Работает схожим образом.

SELECT GROUP_CONCAT(ProductName, ', ')
FROM Products
WHERE CategoryID = 1;

Распространенные ошибки

Попытка использовать агрегатную функцию в выражении без группировки.

SELECT ProductID, STRING_AGG(ProductName, ', ')
FROM Products;
Ошибка: Колонка 'Products.ProductID' недопустима в списке выбора, поскольку не содержится ни в агрегатной функции, ни в предложении GROUP BY.

Использование предложения ORDER BY вне контекста функции.

SELECT STRING_AGG(ProductName, ', ')
FROM Products
ORDER BY Price DESC;
-- Это допустимо, но сортирует уже агрегированный результат, а не порядок конкатенации внутри STRING_AGG.

Игнорирование типа данных результата, что может привести к усечению при длинных строках.

DECLARE @List VARCHAR(100);
SET @List = (SELECT STRING_AGG(ProductName, ', ') FROM Products);
-- Если результат длиннее 100 символов, произойдет усечение.

Изменения в новых версиях

Функция STRING_AGG была введена в SQL Server 2017 (совместимость уровня 140). В более поздних версиях существенных изменений в синтаксисе или поведении не было. Однако, с повышением уровня совместимости базы данных, оптимизатор запросов может использовать улучшенные алгоритмы выполнения для запросов с этой функцией.

Расширенные варианты использования

Конкатенация с фильтрацией и группировкой по нескольким полям.

Пример sql
SELECT
    CategoryID,
    STRING_AGG(ProductName, '; ') WITHIN GROUP (ORDER BY Price) AS Products
FROM Products
WHERE Discontinued = 0
GROUP BY CategoryID;
CategoryID | Products
1 | Product A; Product B
2 | Product X; Product Y

Использование DISTINCT перед выражением через подзапрос.

Пример sql
SELECT STRING_AGG(ProductName, ', ') AS UniqueProducts
FROM (SELECT DISTINCT ProductName FROM Products WHERE CategoryID IN (1,2)) AS T;

Формирование структурированной строки с разными разделителями.

Пример sql
SELECT
    CategoryID,
    STRING_AGG(CONCAT(ProductName, ' (', CAST(Price AS VARCHAR), ' руб.)'), ', ') AS ProductInfo
FROM Products
GROUP BY CategoryID;

Обработка потенциальных повторяющихся разделителей с помощью TRIM.

Пример sql
SELECT
    CategoryID,
    TRIM(',' FROM STRING_AGG(',' + ProductName, '')) AS ProductList
FROM Products
GROUP BY CategoryID;

Использование в оконной функции для накопления строк в порядке.

Пример sql
SELECT
    OrderID,
    ProductID,
    STRING_AGG(ProductName, ', ') OVER (PARTITION BY OrderID ORDER BY ProductID) AS AccumulatedProducts
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID;

MS SQL STRING_AGG function comments

En
STRING AGG Concatenates the values of string expressions and places separator values between them