The same customer list arrives in a different order on the next refresh. WITHIN GROUP gives STRING_AGG an explicit ordering rule. Without that rule, the display order is an accident of the execution plan.
Give the Aggregate an Order With WITHIN GROUP
STRING_AGG combines values with a separator. SQL Server introduced it in 2017. Its ordering clause requires database compatibility level 110 or higher. The ORDER BY inside WITHIN GROUP controls the aggregate's input order.
An ORDER BY after GROUP BY controls the result rows instead. One doesn't substitute for the other. Keep that distinction visible when reviewing a list-building query.
I ask whether the list is a display or a data exchange format. A comma-separated display is useful. A machine-readable payload needs escaping rules when values contain commas.
Don't assume a separator makes arbitrary strings unambiguous. If another application must recover individual values, use a structured format or pass rows. The list should serve the consumer's actual contract.
Build a List for Each Customer
The sample table deliberately includes duplicates and a missing product name. It is a session-local temporary table, so use one query window. The first aggregate orders product names within each customer.
It also orders the customer result rows separately. Those two clauses work at different levels. Inspect the results before changing the data or adding other joins.
Cast the input expression to nvarchar(max), not the result after aggregation. The input type determines the aggregate's output type and limit. The Unicode separator matches the Unicode expression.
An outer cast cannot rescue an aggregate that already exceeded its fixed-length limit. I make that choice explicit before the list grows into a production error at an inconvenient time.
CREATE TABLE #CustomerProducts
(
CustomerId int NOT NULL,
ProductName nvarchar(100) NULL
);
INSERT #CustomerProducts VALUES
(1,N'Wheel'),(1,N'Frame'),(1,N'Wheel'),(1,NULL),(2,N'Brake');
SELECT CustomerId,
STRING_AGG(CONVERT(nvarchar(max),ProductName),N', ')
WITHIN GROUP (ORDER BY ProductName) AS ProductList
FROM #CustomerProducts
GROUP BY CustomerId
ORDER BY CustomerId;Remove Duplicates Before Aggregating
STRING_AGG doesn't accept a DISTINCT keyword inside its expression in SQL Server. Deduplicate in a subquery or CTE first. The grouping identity belongs in that deduplication.
Removing duplicates across the whole input without CustomerId merges unrelated customers. Keep the business key beside the displayed value when preparing the aggregate input.
Also check why duplicates exist. Multiple purchases of one product can be legitimate. A faulty join multiplying rows is a different problem. DISTINCT is appropriate for a unique product catalog per customer.
It doesn't repair an incorrect join when quantities or amounts matter. Review the source grain before choosing a deduplication step. The query should express the list's intended meaning.
WITH UniqueProducts AS
(
SELECT DISTINCT CustomerId, ProductName
FROM #CustomerProducts
)
SELECT CustomerId,
STRING_AGG(CONVERT(nvarchar(max),ProductName),N', ')
WITHIN GROUP (ORDER BY ProductName) AS ProductList
FROM UniqueProducts
GROUP BY CustomerId;
Place Missing Values Inside WITHIN GROUP
The aggregate skips NULL inputs and doesn't add a separator for them. An empty string is different. It remains an input and can produce an empty-looking item.
Normalize blank names deliberately if that is the business rule. Don't treat every missing representation as identical without inspecting the source. That distinction explains several mysterious extra separators in real reports.
Use ISNULL before aggregation to display a placeholder for a NULL value. Cast first so the placeholder isn't truncated to the source expression's narrow width. Decide where that placeholder sorts.
The ORDER BY expression can include a null-ranking rule and the product name. A user-visible list needs a predictable position for missing data, not only a predictable separator.
SELECT CustomerId,
STRING_AGG(ISNULL(CONVERT(nvarchar(max),ProductName),N'(unnamed)'),N', ')
WITHIN GROUP
(ORDER BY CASE WHEN ProductName IS NULL THEN 1 ELSE 0 END, ProductName)
AS ProductList
FROM #CustomerProducts
GROUP BY CustomerId;Understand the Fixed-Length Limits
A varchar input without max produces a varchar result limited to 8,000 bytes. An nvarchar input without max produces an nvarchar result limited to 4,000 characters. That Unicode limit occupies 8,000 bytes.
Other nonstring inputs convert to a fixed nvarchar result. These are type rules, not observed list sizes from your server. Choose the expression type before aggregation.
The max type supports longer values, but it doesn't make unlimited output a good interface. A huge list uses memory and network bandwidth. Consider whether the reader can use it.
Return detail rows for drilling into a long catalog. A comma list containing the entire warehouse is technically a list, although it has stopped being a helpful report.
Compare the Older XML Pattern
FOR XML PATH supplied list concatenation before STRING_AGG. The TYPE directive and value method decode XML escaping in the final text. Omitting that decoding leaves entities in names containing special characters.
STUFF removes the leading separator. The ordering belongs inside the correlated subquery. This is another case where an outer ORDER BY cannot govern the assembled string.
Keep the older pattern when supporting an engine without STRING_AGG. On supported versions, the newer aggregate states the intention more clearly. Compare results using names containing ampersands, commas, blanks, and NULL.
The migration should preserve the agreed behavior. Shorter SQL is useful only when the output contract survives the change, including its less attractive edge cases.
SELECT c.CustomerId,
STUFF((SELECT N', ' + p.ProductName
FROM #CustomerProducts AS p
WHERE p.CustomerId = c.CustomerId
ORDER BY p.ProductName
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,N'') AS ProductList
FROM (SELECT DISTINCT CustomerId FROM #CustomerProducts) AS c;Keep the WITHIN GROUP Rule Stable
Collation affects alphabetical sorting. Case and accents follow that collation's rules. Add a stable tie-breaker when the consumer needs a specific order among equivalent displayed names.
Sorting by the same label alone doesn't establish a distinction the collation considers equal. Document whether the business wants alphabetical order, purchase order, or another sequence. Each needs its own expression.
What should this customer see first in the list? Start with that answer and use WITHIN GROUP to encode it. Verify one result per customer after every join.
Test long names and repeated products before deployment. The finished query should explain both membership and order. That makes a refresh repeat the business rule, instead of exposing whatever row order the plan delivered.
Check a customer with no eligible products too. A grouped query returns no row for that customer unless you start from the customer table. Decide whether the report needs NULL, an empty string, or a separate message. That choice belongs outside the concatenation step and should survive changes to the product filter.
Related reading on this blog: STRING_AGG Function to Concatenate Strings and NULL Values and CONCAT Function.
An ordered list is not a lucky scan sequence, it is an explicit aggregate rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
