Complete Guide to String Aggregation in SQL Server: From FOR XML to STRING_AGG

Introduction and Problem Context

In database development, there is frequent need to aggregate multiple rows of data into single strings based on specific groupings. This requirement is particularly common in report generation, data export, and business logic processing. SQL Server, as a mainstream relational database management system, provides multiple technical solutions for string aggregation.

Core Solution: FOR XML PATH Method

For users of SQL Server 2005 and later versions, FOR XML PATH combined with the STUFF function offers an efficient and flexible string aggregation solution. The core principle of this method utilizes XML serialization mechanism to convert multiple rows of data into a single XML string, then removes excess separators through string processing functions.

Basic Implementation Code

-- Create temporary test table
CREATE TABLE #SampleTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #SampleTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #SampleTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #SampleTable ([ID],[Name],[Value]) VALUES (2,'C',9)

-- Implement string aggregation using FOR XML PATH
SELECT 
  [ID],
  STUFF(
    (
      SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
      FROM #SampleTable 
      WHERE (ID = Results.ID) 
      FOR XML PATH(''), TYPE
    ).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #SampleTable Results
GROUP BY ID

-- Clean up temporary table
DROP TABLE #SampleTable

Technical Principle Deep Analysis

The FOR XML PATH('') subquery serializes qualifying multiple rows of data into XML format, where the empty string parameter ensures no outer XML tags are generated. The TYPE directive preserves XML data type for subsequent processing. The value('(./text())[1]','VARCHAR(MAX)') method extracts text content from XML nodes, effectively avoiding XML entity issues.

The STUFF function plays a crucial role here, with syntax STUFF(string, start, length, replacement). By specifying starting position 1 and length 2, it removes the extra comma and space separators at the beginning of the subquery result, achieving clean string output.

Modern Solution: STRING_AGG Function

SQL Server 2017 and subsequent versions introduced the native string aggregation function STRING_AGG, greatly simplifying string aggregation operations. This function is specifically designed for grouped string concatenation, featuring concise syntax and excellent performance.

STRING_AGG Basic Usage

SELECT 
  id, 
  STRING_AGG(CONCAT(name, ':', [value]), ', ') AS NameValues
FROM #YourTable 
GROUP BY id

Advanced Features and Sorting Control

STRING_AGG supports WITHIN GROUP clause, allowing sorting of aggregated strings:

SELECT 
  City,
  STRING_AGG(EmailAddress, ';') WITHIN GROUP (ORDER BY EmailAddress ASC) AS Emails
FROM Person.BusinessEntityAddress
INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID
INNER JOIN Person.EmailAddress ON BusinessEntityAddress.BusinessEntityID = EmailAddress.BusinessEntityID
GROUP BY City

Performance Comparison and Technical Selection

Different string aggregation methods show significant differences in performance. Based on actual test data:

• FOR XML PATH method averages approximately 108 milliseconds
• CLR custom function solution around 80 milliseconds
• Scalar UDF with GROUP BY about 452 milliseconds
• Recursive CTE solution performs worst at 70,240 milliseconds

STRING_AGG, as a native function, typically performs optimally in compatible versions, especially when processing large datasets.

Special Character Handling and Edge Cases

When using FOR XML PATH method, special attention must be paid to XML special character escaping. When strings contain XML reserved characters like <, >, &, they are automatically converted to corresponding XML entities. The TYPE).value() method effectively resolves this issue.

For cases involving non-serializable characters like 0x001A, FOR XML will throw errors. In such scenarios, conversion to binary format or alternative solutions should be considered.

Data Type Conversion Best Practices

During string aggregation, implicit conversion of non-string types follows SQL Server standard conversion rules. Explicit use of CAST or CONVERT functions is recommended to ensure data type consistency:

-- Explicit type conversion example
SELECT 
  [ID],
  STUFF(
    (
      SELECT ', ' + [Name] + ':' + CONVERT(VARCHAR(10), [Value]) 
      FROM #SampleTable 
      WHERE ID = Results.ID 
      FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #SampleTable Results
GROUP BY ID

Practical Application Scenario Expansion

String aggregation technology has wide applications in real business scenarios:

• Email List Generation: Aggregate employee email addresses by city
• Tag Systems: Flatten many-to-many relationships between articles and tags
• Report Generation: Summarize detailed transaction records into summary information
• Data Export: Prepare data in specific formats for external systems

Version Compatibility Strategy

For different SQL Server versions, the following strategies are recommended:

• SQL Server 2017+: Prioritize STRING_AGG function
• SQL Server 2005-2016: Adopt FOR XML PATH solution
• SQL Server 2000: Consider scalar UDF or cursor solutions
• Cross-version Compatibility: Implement version detection and dynamic SQL in stored procedures

Conclusion and Best Practices

String aggregation is an important technology in SQL Server data processing. The FOR XML PATH method provides reliable solutions in earlier versions, while the STRING_AGG function significantly simplifies implementation complexity in modern versions. Developers should choose appropriate technical solutions based on database version, performance requirements, and business scenarios in practical applications, while paying attention to details such as special character handling and data type conversion.

With the continuous evolution of SQL Server, string aggregation functionality will be further improved, providing developers with more convenient and efficient data processing tools.