Before SQL Server 2017 came along, there wasn’t a T-SQL equivalent of the MySQL GROUP_CONCAT() function. This function allows you to return a result set as a comma-separated list, as opposed to listing each row as a separate row (as with a normal result set).

Prior to SQL Server 2017, if you wanted to put your result into a comma separated list, you’d need to find a workaround, perhaps using a combination of STUFF() and FOR XML PATH().

However, T-SQL now has the STRING_AGG() function which is available from SQL Server 2017. This function does pretty much the same thing as MySQL’s GROUP_CONCAT() function (with some minor differences).

Syntax

The syntax of the STRING_AGG() function goes like this:

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

Where expression is an expression of any type. Expressions are converted to NVARCHAR or VARCHAR types during concatenation. Non-string types are converted to NVARCHAR type.

Where separator is an exprssion of NVARCHAR or VARCHAR type that is used as separator for concatenated strings. It can be literal or variable.

The (optional) order clause consists of WITHIN GROUP followed by ORDER BY ASC or ORDER BY DESC in parentheses. ASC orders the result in ascending order. This is the default value. DESC orders the result in descending order.

Example

Here’s a quick example of the STRING_AGG() function:

SELECT STRING_AGG(Genre, ',') AS Result
FROM Genres;

Result:

Result                                      
--------------------------------------------
Rock,Jazz,Country,Pop,Blues,Hip Hop,Rap,Punk

As you can see, the result set is listed out as a comma separated list. This is because our second argument is a comma, which specifies that a comma should be used as the separator.

Here’s the same result set but without the STRING_AGG() function:

SELECT Genre AS Result 
FROM Genres;

Result:

Result 
-------
Rock   
Jazz   
Country
Pop    
Blues  
Hip Hop
Rap    
Punk   

So just a normal result set.

For more examples, such as grouping, handling null values, and ordering the results, see How to Return Query Results as a Comma Separated List in SQL Server.