SQL Server’s main string aggregate function is STRING_AGG: it combines values from multiple rows into one text value, inserting a chosen separator between them. Use WITHIN GROUP (ORDER BY ...) when the order matters; otherwise, SQL Server does not promise the order of the concatenated values.
What STRING_AGG does
STRING_AGG is an aggregate function, like SUM or COUNT, but instead of calculating a number it joins string representations of values from a set of rows. It is useful for producing readable lists in reports, exports, summaries, and application queries—for example, listing each customer’s order numbers or each department’s employees in one row.
The function was introduced in SQL Server 2017 (14.x) and is also available in later versions and supported Azure SQL offerings. It can be used in queries at any database compatibility level. The optional ordering clause, WITHIN GROUP, requires database compatibility level 110 or higher. Check a database’s compatibility setting if an ordered example fails even though the SQL Server engine supports the function.
A basic call has this form:
STRING_AGG(expression, separator)
The expression supplies each row’s value, while the separator is placed between values. A comma and space, for example, makes a list easier to read than a bare comma. The separator is not added before the first item or after the last one.
Basic syntax and a simple example
Suppose dbo.Orders has CustomerID and OrderNumber columns. To return one comma-separated list of order numbers per customer, use:
SELECT CustomerID,
STRING_AGG(OrderNumber, ', ') AS OrderNumbers
FROM dbo.Orders
GROUP BY CustomerID;
The GROUP BY creates one result row for each customer, and the aggregate combines that customer’s order numbers. Without a grouping column, STRING_AGG produces one aggregate for the rows selected by the query. Other selected columns generally need to be grouped or aggregated, just as with other SQL aggregate functions.
The expression does not have to be a character column. SQL Server converts non-string values to strings for aggregation. For example, an integer order number can be joined with a comma separator. For clarity and predictable formatting, however, explicitly convert values when their text representation matters—especially dates, decimals, and values requiring a particular format.
Control the order of the values
Rows in a table have no inherent presentation order. An ORDER BY at the end of a query sorts result rows; it does not define the order of values inside the aggregate. Put the ordering instruction inside WITHIN GROUP instead:
SELECT CustomerID,
STRING_AGG(OrderNumber, ', ')
WITHIN GROUP (ORDER BY OrderDate, OrderNumber) AS OrderNumbers
FROM dbo.Orders
GROUP BY CustomerID;
This sorts each customer’s values by date, then by order number when dates tie. Include a tie-breaker if a stable sequence matters: ordering only by a non-unique date leaves the relative order of same-date rows unspecified. You can also order descending, or order by a different column from the one being concatenated.
For instance, a query can concatenate employee names while sorting by hire date. The ordering column does not have to appear in the output list, though it must be available to the query. If alphabetical order is wanted, sort by the name expression or an appropriate normalized column, and consider the database collation because collation rules affect case and accent sorting.
Group related values into lists
A common use is to generate one list for each parent record. If customers and orders are stored separately, join the tables and group on the customer key and any customer attributes you select:
SELECT c.CustomerID,
c.CustomerName,
STRING_AGG(CONVERT(nvarchar(max), o.OrderNumber), N', ')
WITHIN GROUP (ORDER BY o.OrderDate, o.OrderNumber) AS OrderNumbers
FROM dbo.Customers AS c
JOIN dbo.Orders AS o
ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.CustomerName;
The example converts the order number to a Unicode large-value string, uses a Unicode separator, and orders the list. The inner join means customers with no orders do not appear. To retain them, use a left join; because null aggregate inputs are ignored, a customer with no matching order will have a null aggregate result. Use COALESCE around the aggregate if the report should show an empty string or a label instead.
Be alert to joins that multiply rows. If a customer’s orders are joined to multiple address or tag records before aggregation, an order number may appear repeatedly. Fix the join or deduplicate the intended input before aggregating rather than trying to hide a data-shaping problem with a string operation.
NULL values, data types, and output length
STRING_AGG ignores null expression values, and it does not insert a separator for a skipped null. Thus, values A, NULL, and B with a comma separator produce A,B, not A,,B. If nulls need to be represented explicitly, replace them in the expression, for example with ISNULL(ColumnName, N'(missing)'). Decide whether a placeholder is appropriate for the report before doing so.
The expression’s data type determines the aggregate’s return type and maximum length. A bounded nvarchar expression produces an nvarchar(4000) result, while bounded varchar produces varchar(8000). An input of nvarchar(max) or varchar(max) produces the corresponding large-value type. A long aggregate over a bounded input can exceed the limit and raise an error; it does not automatically become a large-value result just because many rows are combined.
Cast the expression to a large-value type when the result may be long, as in CONVERT(nvarchar(max), ColumnName). Match the separator’s character type as well: when the expression is varchar, the separator cannot be nvarchar. Using an N-prefixed Unicode separator with an nvarchar expression is a common, explicit choice.
Distinct values and separators
STRING_AGG does not provide a DISTINCT keyword inside the function call. To list each value once, remove duplicates in a subquery or common table expression, then aggregate that result. For example:
WITH DistinctTags AS
(
SELECT DISTINCT ProductID, Tag
FROM dbo.ProductTags
WHERE Tag IS NOT NULL
)
SELECT ProductID,
STRING_AGG(Tag, ', ')
WITHIN GROUP (ORDER BY Tag) AS Tags
FROM DistinctTags
GROUP BY ProductID;
Deduplicate on every column that defines a distinct item. If the goal is one label per product, the example’s pair of product and tag is the relevant key; deduplicating just the tag across the whole table would change the meaning.
The separator is literal text. SQL Server does not automatically quote values or escape commas, pipes, or other characters inside them. If a value itself contains the chosen separator, a consumer splitting the result may not be able to distinguish that separator from a boundary between values. For data exchange, prefer a structured format such as rows or properly generated JSON where possible. For human-readable output, choose a separator unlikely to occur in the values, or define an escaping convention understood by both producer and reader.
Alternatives for older SQL Server versions
On SQL Server versions earlier than 2017, a widely used workaround is a correlated subquery with FOR XML PATH and STUFF. It can produce a delimited list, but the syntax is more involved and XML characters require care. The TYPE directive and .value extraction in this pattern return decoded text instead of leaving XML entities such as & in the result:
SELECT p.ParentID,
STUFF
(
(
SELECT N', ' + c.Name
FROM dbo.Child AS c
WHERE c.ParentID = p.ParentID
ORDER BY c.Name, c.ChildID
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'),
1, 2, N''
) AS Names
FROM dbo.Parent AS p;
The inner query creates the ordered text with a delimiter before each name. STUFF removes the first delimiter. The explicit ordering and a tie-breaker matter here too. This technique is useful for legacy compatibility, but it is less direct than STRING_AGG and should not be copied without understanding how XML serialization and null values affect the input.
Performance and common mistakes
For ordinary grouped lists, the important factors are usually the number of input rows, the grouping and ordering work, and whether the query plan can use useful indexes. An index beginning with the grouping key and then the ordering columns may help some workloads, but the right index depends on filters, joins, table size, and the execution plan. Measure the real query rather than adding an index solely because an aggregate uses ORDER BY.
- Expecting implicit order: add
WITHIN GROUPwhen sequence is meaningful. - Surprise duplicates: inspect joins and define which columns make an item unique before aggregation.
- Length errors: cast the expression to
varchar(max)ornvarchar(max)when the combined result can exceed the bounded return type. - Missing values: remember that null expressions are skipped; apply an explicit replacement only if desired.
- Unparseable output: do not assume a delimiter makes the result safe to split when values can contain that delimiter.
Also distinguish aggregation from concatenating values across columns in a single row. STRING_AGG combines rows within a group; it is not a replacement for expressions such as CONCAT that combine separate columns from one row.
Frequently Asked Questions
What is the string aggregate function in SQL Server?STRING_AGG is SQL Server’s built-in aggregate for joining values from multiple rows into one string with a separator. It was introduced in SQL Server 2017.
How do I sort values inside STRING_AGG?
Add WITHIN GROUP (ORDER BY column_name) to the function call. Include additional sort columns when needed to make ties predictable. This is separate from the query’s final ORDER BY.
Does STRING_AGG remove duplicates?
No. It concatenates each qualifying input row, including duplicate values. Select distinct values in a subquery or common table expression before calling the aggregate.
How does STRING_AGG handle NULL?
It skips null expressions and does not add a separator for them. Use ISNULL or COALESCE within the expression if nulls should appear as a label or another substitute.
Why does STRING_AGG return an error for a long result?
The output type is based on the expression type. Bounded character inputs have bounded aggregate results, so a large combined value can exceed the limit. Convert the input expression to varchar(max) or nvarchar(max) when a larger result is required.
Related
- Bullseye Graphic in PowerPoint: Make One Cleanly
- Access Split Form: Put the Datasheet on the Bottom
- Balance of Payments: How It Works and What It Shows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Accounting Format: Show Negative Numbers in BracketsYes. In Excel for Microsoft 365 on Windows, the Accounting number format commonly displays negative values in parentheses—often…
- Hanging Indent in Word: A Step-by-Step GuideTo create a hanging indent in Microsoft Word, select the paragraph or paragraphs, open the Paragraph settings, and…
- How to Add a Cover Page in Word and Customize It EasilyIn Word for Microsoft 365 on Windows, place the cursor in your document, select Insert > Pages >…
- How to Split a Page in Excel for Viewing or PrintingTo split an Excel worksheet into independently scrolling panes, select the cell below and to the right of…
- Export Google Contacts to Outlook Without Losing DetailsTo export Google Contacts to Outlook, open Google Contacts, choose Export, and select the Outlook CSV format; then…
- Insert an Image in an Excel Comment Box or NoteTo put a picture inside an Excel comment box, create a legacy note, then set its fill to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.