How to concatenate text from multiple rows into a single text string in SQL Server?

 

In SQL Server, you can concatenate text from multiple rows into a single text string using the FOR XML PATH and STUFF functions. This approach is commonly used for tasks like aggregating values from multiple rows into a comma-separated list. Here's an example:

Suppose you have a table named Orders with the following data:

plaintext

OrderID | ProductName
------- | ------------
1       | Product A
2       | Product B
3       | Product C

You want to concatenate the ProductName values into a single comma-separated string.

You can achieve this with the following SQL query:

sql

SELECT 
    STUFF(
        (
            SELECT ', ' + ProductName
            FROM Orders
            FOR XML PATH('')
        ), 1, 2, '') AS ConcatenatedProducts;

In this query:

• The subquery (SELECT ', ' + ProductName FROM Orders FOR XML PATH('')) is used to concatenate the ProductName values into a single string with a comma and space as the separator. The FOR XML PATH('') is used to ensure that the result is in a single row.

• The STUFF function is then used to remove the leading comma and space (, ) from the concatenated string. The STUFF function has the syntax STUFF(string, start, length, replacement). In this case, we start at position 1 and remove 2 characters (the leading comma and space), replacing them with an empty string.

The result of the query will be:

plaintext

ConcatenatedProducts
---------------------
Product A, Product B, Product C

This query aggregates the ProductName values from multiple rows into a single text string, separated by commas, which can be useful for various reporting and display purposes.