Reliable STRING_AGG Results in SQL Server

A comma-separated list looks like presentation work, but its correctness depends on several database decisions. Which rows belong to the group? Are duplicate values meaningful? Should a missing value leave a placeholder? Is the order stable? STRING_AGG makes the concatenation concise without answering those questions for you.

This article uses SQL Server 2017 or later. The ordered WITHIN GROUP clause requires a compatible database level, at least 110. Check both the engine and database settings when the same statement behaves differently between environments.

Make order and missing values explicit

The practice data deliberately includes a repeated tag and a 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;

Document 1 produces Backup, Tuning, Tuning under the illustrated alphabetical comparison. The NULL contributes neither text nor an extra separator. Document 2 produces Security. STRING_AGG does not remove duplicates, and an outer ORDER BY would only sort the result groups, not the values inside each list.

WITHIN GROUP defines the list order. TagId makes the order unambiguous when tags compare equal. If the business wants assignment order, use its actual sequence instead of alphabetical order. Database collation affects both text comparison and the ordering of accented or differently cased values, so test the names your application really stores.

A NULL tag and an empty string are different. NULL is skipped; an empty string is still a value and can leave an apparently unexplained separator. Normalize empty or whitespace-only values at an agreed boundary if the product considers them absent. Do not silently trim meaningful codes simply to make a report look cleaner.

To show an explicit placeholder for missing values, replace NULL before aggregation. Choose a label that cannot be confused with a real value, or use structured output with a genuine null. A display label such as Unknown changes presentation, not the underlying fact that the value is missing.

Deduplicate the correct input

When the desired list contains unique tags, remove duplicates at the intended grain before concatenating.

;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;

Document 1 now produces Backup, Tuning. DISTINCT applies to DocumentId and Tag together, so the same tag remains available to another document. Deduplicating across the entire table and then trying to reconstruct membership would lose the relationship being reported.

Check joins before adding DISTINCT. If each document joins to several comments and several tags, the intermediate result can multiply tags. Removing duplicates at the end may hide that fanout while other aggregates remain wrong. Aggregate the tag relation independently and then join the resulting one-row-per-document data to the report.

Text equality also matters during deduplication. Under a case-insensitive collation, differently cased spellings may collapse. Decide whether a canonical display spelling is required; DISTINCT alone is not a complete rule for selecting one preferred spelling among equivalent source values.

Control size and the output contract

Convert the input expression to nvarchar(max) before STRING_AGG when long results are legitimate. The return type is derived from the input expression. Converting the completed aggregate afterward is too late to avoid the bounded result type used during aggregation. Keep separator and input types compatible as well.

An unbounded type does not mean an unbounded response is a good application design. A document with hundreds of thousands of related values can require considerable memory and create a huge network payload. Set practical limits, return related records through pagination, or create a separate export path for unusually large groups.

Do not use a comma list as a lossless interchange format when values themselves can contain commas, quotes, or line breaks. FOR JSON can preserve boundaries and escaping for an API. Likewise, a concatenated string should not replace the normalized relationship if you still need to filter, validate, or update individual tags.

Test duplicates, NULL, empty values, multilingual text, separator characters, and a result larger than the ordinary fixed-length limit. If output appears truncated, compare its actual database length with client display limits before changing the query. Reliable aggregation preserves meaning from source rows through the final consumer.

Technical references: Microsoft Learn: STRING_AGG · Microsoft Learn: FOR JSON.