Concatenating values into a single string with SQL Server 2017

   

    STRING_AGG is another great function which will be available with SQL Server 2017. It concatenates the string values and places separator values between them. It doesn't add the separator to the end of the string.

      STRING_AGG returns VARCHAR or NVARCHAR. Depending on your expression values STRING_AGG function might need to convert expression values to string. You may want to check its performance if you want to use it in a heavy queries. STRING_AGG ignores the NULL values. You can use ISNULL function if you to like have a place holder for NULL values.

STRING_AGG ( expression, separator [order clause])

expression can be any type, STRING_AGG function will convert the expression's values to NVARCHAR or VARCHAR type during the concatenation. Non-string values will be converted to NVARCHAR type.

separator must be NVARCHAR or VARCHAR value that is used as separator for concatenated strings.

order clause is optional and it specifies order of concatenated results using WITHIN GROUP clause.

WITHIN GROUP (ORDER BY order by expression list [ASC | DESC]

STRING_AGG function's return type depends on your input expression. 

• VARCHAR input returns VARCHAR
• NVARCHAR input returns NVARCHAR
• Non-String inputs return NVARCHAR type.

     Following example separates the Lastname column's values with carriage return. You need to change the results window's format to Results to Text mode to see the carriage return. Otherwise you will see the output in a row.

       Next example shows how to use ORDER BY option. I am going to order the customers by their state and add carriage return between the values.