Concatenate Multiple Rows in SQL Server: Complete Guide

In SQL Server, you can concatenate multiple rows into a single text string using several methods, with the most modern approach being the STRING_AGG function available in SQL Server 2017 and later, or the traditional FOR XML PATH method for older versions. For your example with names, you would use STRING_AGG(Name, ', ') to get Peter, Paul, Mary.

Contents

• Modern Approach: STRING_AGG Function
• Traditional Method: FOR XML PATH
• Advanced Techniques and Considerations
• Performance Comparison
• Practical Examples with Different Scenarios
• Handling NULL Values and Special Cases

Modern Approach: STRING_AGG Function

The STRING_AGG function is the most straightforward and readable method available in SQL Server 2017 and later versions. This function aggregates values from multiple rows into a single string with a specified separator.

Basic Syntax:

STRING_AGG ( expression, separator ) [ ( order_by_clause ) ]

For your example with names:

SELECT STRING_AGG(Name, ', ') AS ConcatenatedNames
FROM YourTable;

Key Features:

• Automatically handles NULL values (excludes them by default)
• Allows ordering of results using the ORDER BY clause
• More readable and maintainable than FOR XML PATH
• Better performance for large datasets

Example with ordering:

SELECT STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name)
FROM YourTable;

Note: The STRING_AGG function is the recommended approach for modern SQL Server environments due to its simplicity and performance benefits.

Traditional Method: FOR XML PATH

Before SQL Server 2017, developers commonly used the FOR XML PATH method to concatenate multiple rows. This approach leverages XML functionality to join string values.

Basic Syntax:

SELECT STUFF(
    (SELECT ', ' + Name 
     FROM YourTable 
     FOR XML PATH('')),
    1, 2, ''
) AS ConcatenatedNames;

How it works:

1. The subquery concatenates each name with a comma and space
2. FOR XML PATH('') converts the results to XML format
3. STUFF function removes the leading comma and space

Alternative without STUFF:

SELECT REPLACE(
    (SELECT Name + ', ' 
     FROM YourTable 
     FOR XML PATH('')), 
    ', ', ', ') 
AS ConcatenatedNames;

Important: This method is more complex and requires careful handling of separators and potential NULL values.

Advanced Techniques and Considerations

Different Separator Options

You can use various separators depending on your requirements:

-- Space separator
SELECT STRING_AGG(Name, ' ') FROM YourTable;

-- Semicolon separator
SELECT STRING_AGG(Name, '; ') FROM YourTable;

-- New line separator
SELECT STRING_AGG(Name, CHAR(13) + CHAR(10)) FROM YourTable;

Conditional Aggregation

SELECT STRING_AGG(
    CASE WHEN Status = 'Active' THEN Name END, 
    ', '
) FROM YourTable;

Grouped Concatenation

SELECT Department, STRING_AGG(EmployeeName, ', ') 
FROM Employees 
GROUP BY Department;

DISTINCT Values

SELECT STRING_AGG(DISTINCT Name, ', ') FROM YourTable;

Performance Comparison

Performance Testing Results:

• STRING_AGG typically performs 2-3x faster than FOR XML PATH on large datasets
• Memory usage is more efficient with STRING_AGG
• Execution plans show more optimized query plans with the newer function

Practical Examples with Different Scenarios

Example 1: Simple Name Concatenation

-- Sample table structure
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

-- Data: Peter, Paul, Mary in Sales department
INSERT INTO Employees VALUES 
(1, 'Peter', 'Smith', 'Sales'),
(2, 'Paul', 'Johnson', 'Sales'),
(3, 'Mary', 'Williams', 'Sales');

-- Using STRING_AGG
SELECT STRING_AGG(FirstName + ' ' + LastName, ', ') 
FROM Employees 
WHERE Department = 'Sales';

Result: Peter Smith, Paul Johnson, Mary Williams

Example 2: Handling Mixed Data Types

SELECT STRING_AGG(
    CAST(Value AS VARCHAR(100)), 
    ', '
) AS ConcatenatedValues
FROM MixedValuesTable;

Example 3: Creating Comma-Separated Lists for Reports

SELECT 
    OrderID,
    STRING_AGG(ProductName, ', ') WITHIN GROUP (ORDER BY ProductName) AS Products
FROM OrderDetails
GROUP BY OrderID;

Handling NULL Values and Special Cases

NULL Value Handling

-- STRING_AGG excludes NULL by default
SELECT STRING_AGG(Name, ', ') FROM YourTable;

-- Include NULL values as 'NULL' string
SELECT STRING_AGG(
    CASE WHEN Name IS NULL THEN 'NULL' ELSE Name END, 
    ', '
) FROM YourTable;

Empty String Handling

-- Replace empty strings with a placeholder
SELECT STRING_AGG(
    CASE WHEN Name = '' THEN '[Empty]' ELSE Name END, 
    ', '
) FROM YourTable;

Large Result Set Handling

-- Use MAX data type for very long concatenated strings
SELECT STRING_AGG(
    Description, 
    CHAR(13) + CHAR(10)
) WITHIN GROUP (ORDER BY Description) AS LongText
FROM LargeTable;

Security Considerations

-- Prevent SQL injection by parameterizing values
DECLARE @Separator VARCHAR(10) = ', ';
SELECT STRING_AGG(Name, @Separator) FROM YourTable;

For your specific example with the three names (Peter, Paul, Mary), the simplest solution in SQL Server 2017+ would be:

SELECT STRING_AGG(Name, ', ') AS ConcatenatedNames
FROM YourTable;

This will return exactly what you need: Peter, Paul, Mary.

Sources

1. Microsoft Docs - STRING_AGG (Transact-SQL)
2. SQL Server Central - Concatenating Multiple Rows into One String
3. Stack Overflow - Best way to concatenate multiple rows into one string in SQL Server
4. MSSQLTips - STRING_AGG vs FOR XML PATH Performance

Conclusion

• Choose the right method: Use STRING_AGG for SQL Server 2017+ environments as it’s more readable and performs better, while FOR XML PATH remains a viable option for older versions
• Handle NULL values appropriately: Consider whether you want to exclude or include NULL values in your concatenated results
• Consider performance: For large datasets, STRING_AGG typically offers better performance than traditional methods
• Test with your data: Always test concatenation methods with your specific data volume and structure
• Plan for future maintenance: While FOR XML PATH works, STRING_AGG is the modern standard and easier for other developers to understand

The most straightforward solution for your example is using STRING_AGG(Name, ', ') which will easily transform your three name rows into the desired Peter, Paul, Mary format.