Concatenating text from multiple rows into a single text string in SQL Server can be achieved using different methods, depending on the version of SQL Server you are using. The most common approaches involve using the FOR XML PATH method for older versions, and the STRING_AGG function, which was introduced in SQL Server 2017. I’ll explain both methods.

Using STRING_AGG (SQL Server 2017 and later)

The STRING_AGG function simplifies the concatenation of text from multiple rows into a single string. It allows you to specify the delimiter and directly concatenate the values.

SELECT STRING_AGG(YourColumnName, ', ') AS ConcatenatedText
FROM YourTableName;

Replace YourColumnName with the name of the column you wish to concatenate and YourTableName with the name of your table. You can also specify a delimiter of your choice; in this example, I used a comma followed by a space.

Using FOR XML PATH (Older versions of SQL Server)

For older versions of SQL Server that do not support STRING_AGG, you can use the FOR XML PATH method for concatenation. This method is more verbose and involves XML functions.

SELECT STUFF(
    (SELECT ', ' + YourColumnName
     FROM YourTableName
     FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ConcatenatedText;

In this query, STUFF is used to remove the initial delimiter from the concatenated string. FOR XML PATH('') concatenates the values of YourColumnName from each row into a single XML string, and the .value method is used to convert the result into an NVARCHAR(MAX) type, making it a plain text string. The parameters 1, 2, '' in the STUFF function indicate that the first two characters (in this case, the initial comma and space) should be replaced with an empty string, effectively removing them.

Both methods will result in a single text string that concatenates the values from multiple rows, separated by the specified delimiter. Choose the method that suits your SQL Server version and needs.

Practical Example Data

Let’s assume our Employees table has the following data:

Name
John Doe
Jane Smith
Bob Brown

• The result would be:

John Doe; Jane Smith; Bob Brown

Both methods will produce a single string with all the names concatenated together, separated by semicolons and spaces. These examples demonstrate how to concatenate row values into a single string in SQL Server, using either the STRING_AGG function or the FOR XML PATH method, depending on the version of SQL Server you are using.