Introduction

Concatenating text from multiple rows into a single text string is a common task in SQL Server. This operation is useful for generating reports, creating CSV-like outputs, or combining data into a readable format. SQL Server offers multiple techniques for achieving this.

---

Methods to Concatenate Text in SQL Server

1. Using STRING_AGG() (SQL Server 2017 and Later)

The STRING_AGG() function simplifies concatenating text from multiple rows into one string.

Syntax:

sqlCopy codeSTRING_AGG (expression, separator)  

Example:

Suppose you have a table Employees:

EmployeeID	Name	Department
1	Alice	HR
2	Bob	IT
3	Charlie	IT

To concatenate all names in the IT department:

sqlCopy codeSELECT STRING_AGG(Name, ', ') AS Names  
FROM Employees  
WHERE Department = 'IT';  

Output:

Names
Bob, Charlie

---

2. Using FOR XML PATH (SQL Server 2005 and Later)

For earlier versions of SQL Server, FOR XML PATH is a powerful workaround.

Example:

sqlCopy codeSELECT STUFF((  
    SELECT ', ' + Name  
    FROM Employees  
    WHERE Department = 'IT'  
    FOR XML PATH('')  
), 1, 2, '') AS Names;  

Output:

Names
Bob, Charlie

Explanation:

• FOR XML PATH('') generates a concatenated string of names.
• STUFF() removes the leading separator (, ) for cleaner output.

---

3. Using Recursive CTE (Common Table Expression)

A recursive approach can also be used for concatenation.

Example:

sqlCopy codeWITH EmployeeCTE AS (  
    SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) AS RowNum  
    FROM Employees  
),  
RecursiveCTE AS (  
    SELECT RowNum, Name AS Names  
    FROM EmployeeCTE  
    WHERE RowNum = 1  
    UNION ALL  
    SELECT e.RowNum, r.Names + ', ' + e.Name  
    FROM EmployeeCTE e  
    INNER JOIN RecursiveCTE r  
    ON e.RowNum = r.RowNum + 1  
)  
SELECT TOP 1 Names  
FROM RecursiveCTE  
ORDER BY RowNum DESC;  

---

4. Concatenating with COALESCE()

If you don’t have access to modern SQL functions, you can use a variable and COALESCE() to build the string.

Example:

sqlCopy codeDECLARE @Names NVARCHAR(MAX);  
SET @Names = '';  

SELECT @Names = COALESCE(@Names + ', ', '') + Name  
FROM Employees  
WHERE Department = 'IT';  

SELECT @Names AS Names;  

Output:

Names
Bob, Charlie

---

Performance Considerations

• STRING_AGG is efficient and modern: Use it if you are on SQL Server 2017 or later.
• Large datasets: For large result sets, ensure your query is optimized, and avoid concatenating within loops.
• Use indexes: Proper indexing on filter columns can improve performance for FOR XML PATH and recursive methods.

---

Conclusion

Concatenating text from multiple rows into a single string is a common SQL Server task with multiple solutions:

• Use STRING_AGG for simplicity and performance (SQL Server 2017+).
• Use FOR XML PATH for earlier versions.
• Consider recursive CTEs or COALESCE() for specific scenarios.

Choose the method that best fits your SQL Server version and data requirements.

---

FAQ

Q: What is the recommended approach for SQL Server 2017 or later?
A: Use STRING_AGG for its simplicity and efficiency.

Q: Can I use these methods with GROUP BY?
A: Yes, all methods can be adapted for grouped concatenation.

Start using these techniques to manage and present your SQL Server data effectively!