Concatenating Text from Multiple Rows into a Single String in SQL Server

Introduction

In SQL Server, it’s often necessary to transform data across multiple rows into a single concatenated string. This is especially useful when summarizing information or preparing reports. Several methods are available to achieve this, each suitable for different versions of SQL Server and specific scenarios.

Using FOR XML PATH

The FOR XML PATH method has been a popular way to concatenate text in SQL Server since version 2005. It leverages XML functions to concatenate row values into a single string efficiently.

Example

Suppose you have a table named STUDENTS, structured as follows:

| SubjectID | StudentName |
|———–|————-|
| 1 | Mary |
| 1 | John |
| 1 | Sam |
| 2 | Alaina |
| 2 | Edward |

To concatenate student names per subject, you can use the following query:

SELECT 
    ST2.SubjectID,
    STUFF(
        (
            SELECT ',' + ST1.StudentName AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.StudentName
            FOR XML PATH (''), TYPE
        ).value('text()[1]', 'nvarchar(max)'), 
    1, 1, '') AS Students
FROM dbo.Students ST2
GROUP BY ST2.SubjectID;

Explanation:

• FOR XML PATH('') generates an XML representation of the concatenated results.
• The comma is appended before each name to separate them in the final string.
• STUFF removes the leading comma from the result.

Using COALESCE

For simpler scenarios, especially when dealing with smaller data sets or non-grouped concatenation, you can use COALESCE.

Example

DECLARE @Names NVARCHAR(MAX);
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People;

Explanation:

• This query uses a variable to accumulate names into a single string.
• COALESCE helps in initializing the variable and prevents extra separators.

Using STRING_AGG (SQL Server 2017+)

Starting with SQL Server 2017, you can use the built-in function STRING_AGG, which simplifies row concatenation significantly.

Example

SELECT 
    GroupName, 
    STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

Explanation:

• STRING_AGG directly combines values from multiple rows into a single string.
• It allows for optional ordering and grouping.

Handling NULLs

When dealing with potential NULL values in your data, ensure they are appropriately handled to avoid incorrect results. The COALESCE method can be adapted:

DECLARE @Names NVARCHAR(MAX);
SELECT @Names = COALESCE(@Names + ', ', '') + ISNULL(Name, 'N/A') FROM People;

Explanation:

• Replaces NULL values with a placeholder like 'N/A'.

Best Practices

• Choose the Right Method: Depending on your SQL Server version and requirements (e.g., ordering), select an appropriate method.
• Consider Performance: For large datasets, performance can vary between methods. STRING_AGG is generally more efficient for newer versions.
• Handle NULLs Appropriately: Ensure that any NULL values in your dataset are managed to avoid unexpected results.

By understanding these techniques and selecting the right one based on your SQL Server version and requirements, you can efficiently concatenate data across multiple rows into a single string.