Here is an introduction to String_agg in Microsoft SQL Server. by our in-house experts. Our SQL Server Support team is here to lend a hand with your queries and issues.

String_agg in Microsoft SQL Server

Before SQL Server 2017 (SQL Server 2014 below), concatenating rows of strings into one column can do using the STUFF function that combines with FOR XML PATH.

Today, let us explore SQL’s STRING_AAG function and see how we can concatenate rows of strings into one column string using a separator.

What’s SQL’s STRING_AAG Function?

• Firstly, it is an aggregate function that concatenates strings into a single line separated by a separator.
• Then, the separator is not appended to the end of the result string.
• Next, this function was introduced into SQL Server 2017 and later.

Syntax

STRING_AGG(string expression, separator) [order clause]

• The string expression can any type.
  • It can convert into VARCHAR or NVARCHAR during concatenation.
  • Non-string types are convert to NVARCHAR types.
• The separator is used as a separator for the concatenat strings.
  • It can literal or variable. Just remember it doesn’t add itself at the end of the result string.
• Then, the order clause is the sort order of the result string using the WITHIN GROUP clause.

Syntax of WITHIN GROUP

WITHIN GROUP (ORDER BY expression [ASC | DESC])

• The expression can use for sorting results; only one expression is allow per query.
  • The default order is ascending.

Conclusion

To wrap up, our Support Engineers introduce to String_agg in Microsoft SQL Server. We also learned about the root cause behind this specific error.