Technical Implementation and Optimization of Combining Multiple Rows into One Row in SQL Server

Introduction

In database application development, there is often a need to combine specific field values from multiple records into a single string. This requirement is particularly common in scenarios such as report generation, data export, and interface display. SQL Server provides multiple implementation methods, each with its unique advantages and applicable scenarios.

Problem Background and Requirement Analysis

Consider a typical business scenario: in the customfieldvalue table of the Jira system, for a specific custom field (CUSTOMFIELD = 12534) and issue (ISSUE = 19602), there are multiple records, each containing a STRINGVALUE field. The business requirement is to combine all these STRINGVALUE values into a single comma-separated string.

The original query results are as follows:

SELECT * 
FROM Jira.customfieldvalue
WHERE CUSTOMFIELD = 12534
AND ISSUE = 19602

The expected output format is:

Araç Listesi (C2, K1 vb.Belgeler; yoksa Ruhsat Fotokopileri), Min. 5
araç plakası için İnternet Sorgusu, Son 3 Yıla Ait Onaylı Yıl Sonu
Bilanço + Gelir Tablosu, Son Yıl (Yıl Sonuna ait) Detay Mizanı, İçinde
Bulunduğumuz Yıla ait Ara Dönem Geçici Vergi Beyannamesi, Bayi Yorum
E-Maili, Proforma Fatura

Core Technical Solutions

Variable Concatenation Method

This is the most direct and efficient solution, particularly suitable for scenarios requiring a single combined string. This method utilizes T-SQL variables and string concatenation capabilities:

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534
    AND ISSUE = 19602

SELECT @combinedString as StringValue

Technical Analysis:

• The COALESCE function handles the initial NULL value, ensuring no extra separator appears during the first concatenation
• The variable @combinedString gradually accumulates each STRINGVALUE value within the SELECT statement
• This method operates directly in memory, avoiding complex query plans and achieving higher execution efficiency

XML PATH Method

Another commonly used method leverages SQL Server's XML functionality, which is particularly useful when needing to return results alongside other columns:

SELECT DISTINCT Issue, Customfield, StringValues
FROM Jira.customfieldvalue v1
CROSS APPLY ( SELECT StringValues + ',' 
              FROM jira.customfieldvalue v2
              WHERE v2.Customfield = v1.Customfield 
                  AND v2.Issue = v1.issue 
              ORDER BY ID 
                  FOR XML PATH('') )  D ( StringValues )
WHERE customfield = 12534
    AND ISSUE = 19602

Or using a more concise STUFF function variant:

SELECT (STUFF((
        SELECT ', ' + StringValue
        FROM Jira.customfieldvalue
        WHERE CUSTOMFIELD = 12534
        AND ISSUE = 19602
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS StringValue

Technical Key Points:

• FOR XML PATH('') converts multiple row results into an XML string
• The STUFF function removes the extra separator at the beginning
• This method is more suitable for use as an expression in complex queries

Performance Comparison and Optimization Recommendations

Execution Efficiency Analysis

In practical testing, the variable concatenation method typically demonstrates better performance, especially when processing large amounts of data:

• Variable concatenation method: Direct memory operations with simple query plans
• XML method: Involves XML parsing and string processing with relatively higher overhead

Memory Usage Considerations

When the combined string might be very long, it is recommended to use VARCHAR(MAX) or NVARCHAR(MAX) data types to avoid string truncation issues.

Separator Handling Optimization

In practical applications, separator handling requires special attention:

-- More robust separator handling
DECLARE @result NVARCHAR(MAX) = ''
SELECT @result = @result + CASE WHEN @result = '' THEN '' ELSE ', ' END + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534 AND ISSUE = 19602

Extended Application Scenarios

Multiple Field Combination

In actual business scenarios, there might be a need to combine multiple fields:

DECLARE @combinedInfo NVARCHAR(MAX)
SELECT @combinedInfo = COALESCE(@combinedInfo + ' | ', '') + 
    stringvalue + ' (' + CONVERT(VARCHAR, created) + ')'
FROM jira.customfieldValue
WHERE customfield = 12534 AND ISSUE = 19602

Grouped Combination

For scenarios requiring combination by different dimensions:

SELECT 
    customfield,
    STUFF((
        SELECT ', ' + stringvalue
        FROM jira.customfieldValue v2
        WHERE v2.customfield = v1.customfield
        FOR XML PATH('')
    ), 1, 2, '') AS combined_values
FROM jira.customfieldValue v1
GROUP BY customfield

Best Practices Summary

Based on performance testing and practical application experience, the following best practices are recommended:

1. For simple single-string return requirements, prioritize the variable concatenation method
2. Consider using the XML PATH method in complex queries requiring return alongside other columns
3. Always consider string length limitations and use appropriate data types
4. Conduct thorough performance testing in production environments
5. Consider using the STRING_AGG function available in SQL Server 2017 and later versions (if available)

Conclusion

Combining multiple rows into a single row is a common requirement in SQL Server development. This article has detailed the technical principles and application scenarios of two main implementation methods. The variable concatenation method, with its concise and efficient characteristics, serves as the preferred solution for most scenarios, while the XML PATH method demonstrates its unique value in specific complex queries. Developers should choose appropriate technical solutions based on specific business requirements and performance considerations, paying attention to memory usage and performance optimization in practical applications.