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

To concatenate text from multiple rows into a single text string in SQL Server, you can use the following methods:

1. STRING_AGG (SQL Server 2017 and later)

The recommended and simplest method for modern versions:

SELECT 
  STRING_AGG(ColumnName, ', ') AS ConcatenatedResult
FROM TableName;

Example:

-- Concatenate names from an Employees table into a comma-separated list
SELECT 
  DepartmentID,
  STRING_AGG(EmployeeName, ', ') AS EmployeesList
FROM Employees
GROUP BY DepartmentID;

Features:

• Ordering: Add WITHIN GROUP (ORDER BY ...) for sorted results:

  STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY EmployeeName) AS SortedList

• Ignore NULLs: STRING_AGG automatically skips NULL values.
• Custom Separator: Replace ', ' with any delimiter (e.g., ';').

2. FOR XML PATH (Legacy Method for Older Versions)

Works in SQL Server 2005 and later:

SELECT 
  STUFF(
    (SELECT ', ' + ColumnName
     FROM TableName
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 
  1, 2, ''
  ) AS ConcatenatedResult;

Example:

-- Concatenate employee names grouped by department
SELECT 
  DepartmentID,
  STUFF(
    (SELECT ', ' + EmployeeName
     FROM Employees e2
     WHERE e2.DepartmentID = e1.DepartmentID
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 
  1, 2, ''
  ) AS EmployeesList
FROM Employees e1
GROUP BY DepartmentID;

Breakdown:

• FOR XML PATH(''): Concatenates rows into an XML string.
• STUFF(..., 1, 2, ''): Removes the leading comma and space.
• .value('.', 'NVARCHAR(MAX)'): Converts XML to a string and avoids XML entity escaping (e.g., & → &).

Key Notes

• Handling Duplicates: Use DISTINCT inside the subquery:

  STRING_AGG(DISTINCT ColumnName, ', ')  -- For STRING_AGG
  -- or
  (SELECT DISTINCT ', ' + ColumnName ...) -- For FOR XML PATH

• Performance: STRING_AGG is more efficient than FOR XML PATH.
• NULL Values: Both methods skip NULL by default. Use COALESCE to include them:

  STRING_AGG(COALESCE(ColumnName, 'N/A'), ', ')

Summary

• Modern Use: STRING_AGG (simpler and faster).
• Legacy Systems: FOR XML PATH.
• Customization: Add sorting, delimiters, or handle NULL values as needed.