How to Concatenate Text from Multiple Rows into a Single Text String in SQL Server

Sql Sql Server Csv String Concatenation Group Concat

Are you looking for a way to combine text from multiple rows into a single string in SQL Server? This article will guide you through various methods and techniques to achieve this goal.

Table of Contents

Method 1: Using the COALESCE Function

The COALESCE function is a versatile function that can be used to concatenate values from multiple rows into a single string.

DECLARE @names VARCHAR(MAX)
SELECT @names = COALESCE(@names + ', ', '') + name
FROM YourTable
SELECT @names

In this example, we declare a variable @names of type VARCHAR(MAX) to store the concatenated string. We use the COALESCE function to check if the variable is null or empty. If it is, we initialize it with an empty string, otherwise, we append the comma and the name from each row to the existing value in the variable.

Method 2: Using the FOR XML Path Clause

The FOR XML Path clause is another efficient way to concatenate values from multiple rows into a single string.

SELECT STUFF((SELECT ', ' + name
FROM YourTable
FOR XML PATH('')), 1, 2, '')

In this example, we use the STUFF function to remove the leading comma and space from the concatenated string. The FOR XML PATH('') clause is used to concatenate the values with a comma and space separator.

Method 3: Using STRING_AGG Function (Available in SQL Server 2017 and later)

If you are using SQL Server 2017 or later, you can use the STRING_AGG function to easily concatenate values from multiple rows.

SELECT STRING_AGG(name, ', ') AS names
FROM YourTable

The STRING_AGG function takes two arguments: the column to concatenate and the separator. In this example, we specify the column name and the separator as comma and space.

Conclusion

By using any of the methods mentioned above, you can easily concatenate text from multiple rows into a single text string in SQL Server. Whether you choose to use the COALESCE function, the FOR XML Path clause, or the STRING_AGG function, these techniques will help you achieve your goal efficiently.

References

Related Questions