Надёжное формирование списков STRING_AGG в SQL Server

Список через запятую выглядит задачей отображения, но его корректность зависит от модели данных. Какие строки входят в группу? Нужны ли повторы? Следует ли показывать место отсутствующего значения? Каков порядок элементов? STRING_AGG сокращает запись объединения, но не отвечает на эти вопросы.

Примеры рассчитаны на SQL Server 2017 и новее. Упорядоченная форма WITHIN GROUP требует уровня совместимости не ниже 110. Если код по-разному работает в окружениях, проверяйте одновременно версию движка и настройки базы.

Явно определяем порядок и пропуски

Учебные данные намеренно содержат повторяющийся тег и NULL.

DECLARE @Tags table (DocumentId int, TagId int, Tag nvarchar(100));
INSERT @Tags VALUES
(1, 1, N'Tuning'), (1, 2, N'Backup'), (1, 3, N'Tuning'),
(1, 4, NULL), (2, 5, N'Security');

SELECT DocumentId,
       STRING_AGG(CONVERT(nvarchar(max), Tag), N', ')
       WITHIN GROUP (ORDER BY Tag, TagId) AS TagList
FROM @Tags
GROUP BY DocumentId;

Для документа 1 при показанном алфавитном сравнении получится Backup, Tuning, Tuning. NULL не добавляет ни текста, ни отдельного разделителя. Для документа 2 получится Security. STRING_AGG не удаляет повторы. Внешний ORDER BY сортировал бы готовые группы, а не элементы внутри списка.

Внутренний порядок определяет WITHIN GROUP. TagId разрешает равенство сравниваемых тегов. Если бизнес ожидает порядок назначения, используйте настоящую последовательность назначения. Collation влияет на сравнение, регистр и акценты. Проверяйте именно те многоязычные значения, которые содержит приложение.

NULL отличается от пустой строки. NULL пропускается, а пустой текст остаётся элементом и может создавать непонятный разделитель. Согласуйте границу нормализации пустых значений и строк из пробелов. Не обрезайте значимые коды только ради красивого отчёта.

Для отображения отсутствия заменяйте NULL перед агрегацией. Метка не должна путаться с реальным значением. Структурированный результат может сохранить настоящий null. Надпись Неизвестно меняет представление, но не создаёт отсутствующий исходный факт.

Убираем повторы на нужном уровне

Если список должен содержать уникальные теги, удалите повторы до объединения в правильной связи.

;WITH DistinctTags AS (
    SELECT DISTINCT DocumentId, Tag
    FROM @Tags
    WHERE Tag IS NOT NULL
)
SELECT DocumentId,
       STRING_AGG(CONVERT(nvarchar(max), Tag), N', ')
       WITHIN GROUP (ORDER BY Tag) AS TagList
FROM DistinctTags
GROUP BY DocumentId;

Документ 1 теперь получает Backup, Tuning. DISTINCT применяется совместно к DocumentId и Tag, поэтому тот же тег остаётся допустимым у другого документа. Глобальное устранение повторов без принадлежности документу потеряло бы нужное отношение.

До добавления DISTINCT проверьте соединения. Несколько комментариев и несколько тегов одного документа способны перемножить промежуточные строки. Устранение повторов в конце замаскирует это, пока другие суммы останутся неправильными. Сначала агрегируйте связь тегов отдельно, затем соединяйте результат с одной строкой на документ.

Правила равенства тоже важны. Нечувствительная к регистру collation может объединять разные написания. Если нужна предпочтительная форма отображения, задайте правило её выбора отдельно. DISTINCT сам по себе не определяет каноническое написание среди эквивалентных исходных значений.

Контролируем размер и формат

Преобразуйте входное выражение в nvarchar(max) до STRING_AGG, если длинные результаты допустимы. Возвращаемый тип выводится из входа. Преобразование уже готового агрегата не исправит ограничение, действовавшее во время построения. Тип разделителя также должен соответствовать типу данных.

Большой тип не делает безграничный ответ хорошим интерфейсом. Сотни тысяч связанных значений могут потребовать памяти и создать огромную сетевую передачу. Вводите практические ограничения, возвращайте записи страницами либо выделяйте отдельный экспорт. Не используйте наличие max как замену требованиям к объёму ответа.

Список через запятую не сохраняет точную структуру, если значения сами содержат запятые, кавычки или переводы строк. FOR JSON предоставляет границы и экранирование для API. Строка также не должна заменять нормализованную связь, если отдельные теги нужно искать, проверять или изменять.

Проверяйте дубликаты, NULL, пустые значения, разные языки, разделители внутри текста и длинный результат. При кажущемся обрезании сравните фактическую длину в базе с ограничением отображения клиента. Дополнительно проверьте обработку группы без непустых значений: отсутствие списка не обязательно означает отсутствие самого документа. Надёжная агрегация сохраняет смысл до конечного получателя.

Техническая документация: Microsoft Learn: STRING_AGG · Microsoft Learn: FOR JSON.