Multiple Approaches for Field Value Concatenation in SQL Server: Implementation and Performance Analysis

Introduction

In database application development, there is frequent need to merge multiple query results into a single string row, such as generating comma-separated lists, constructing dynamic SQL statements, or creating report summaries. This operation is commonly referred to as field value concatenation or string aggregation in SQL Server. Based on a typical technical Q&A scenario, this paper thoroughly examines multiple methods for implementing field value concatenation in SQL Server and their technical details.

Problem Scenario Analysis

Assuming there exists a table Table_X containing two fields: ModuleID and ModuleValue. When executing the query SELECT ModuleValue FROM Table_X WHERE ModuleID=@ModuleID, multiple rows of results may be returned:

Value 1
Value 2
Value 3
...

The actual requirement is to combine these values into a single string row: "Value 1, Value 2, Value 3". This need commonly arises when generating CSV-formatted data, building IN condition lists, or displaying associated data summaries.

Core Solution: Variable Assignment Concatenation Method

In SQL Server 2005 and later versions, the most classic and efficient solution is using the variable assignment concatenation method. This approach leverages SQL Server's variable assignment feature to gradually build the result string during query execution:

DECLARE @result VARCHAR(500)
SET @result = ''
SELECT @result = @result + ModuleValue + ', '
FROM TableX
WHERE ModuleId = @ModuleId

The implementation principle of this method is based on SQL Server's execution mechanism:

1. First declare and initialize a string variable @result
2. Perform iterative assignment operations on the variable within the SELECT statement
3. SQL Server sequentially appends each ModuleValue to the variable according to the query result order
4. Each iteration adds the delimiter ", " after the value

The main advantages of this method include:

• Excellent Performance: Completes all operations in a single query, avoiding multiple database accesses
• Good Compatibility: Applicable to SQL Server 2005 and all subsequent versions
• High Flexibility: Allows easy customization of delimiters and formats

Optimization Solution: COALESCE Function Processing

To address the issue of trailing excess delimiters that may occur with variable concatenation, the COALESCE function can be used for optimization:

DECLARE @csv VARCHAR(1000)
SELECT @csv = COALESCE(@csv + ',', '') + ModuleValue
FROM Table_X
WHERE ModuleID = @ModuleID

The clever aspect of this approach lies in:

1. The COALESCE function checks whether the @csv variable is empty
2. If @csv is not empty, it adds a comma delimiter after the existing value
3. If @csv is empty, it starts concatenation with an empty string
4. This avoids the extra delimiter at the end of the final result

It is important to note that if the ModuleValue field is not of string type, explicit type conversion is required:

SELECT @csv = COALESCE(@csv + ',', '') + CAST(ModuleValue AS VARCHAR(50))
FROM Table_X
WHERE ModuleID = @ModuleID

Alternative Solution: XML PATH Method

In SQL Server 2005, the XML PATH method can also be used to implement string concatenation:

SELECT STUFF(
    (SELECT ',' + ModuleValue
     FROM Table_X
     WHERE ModuleID = @ModuleID
     FOR XML PATH('')), 1, 1, '')

The working principle of this method:

1. FOR XML PATH('') converts query results to XML format
2. By specifying an empty path, all values are merged into a single XML text node
3. The STUFF function removes the leading extra delimiter

Limitations of the XML PATH method:

• Requires handling XML special character escaping
• Performance may be inferior to variable concatenation method
• Code readability is relatively poor

Modern Solution: STRING_AGG Function

For SQL Server 2017 and later versions, the built-in STRING_AGG function is recommended:

SELECT STRING_AGG(ModuleValue, ',') AS ConcatenatedValues
FROM Table_X
WHERE ModuleID = @ModuleID

Advantages of the STRING_AGG function:

• Concise and intuitive syntax
• Performance optimized
• Supports ORDER BY clause for controlling concatenation order
• Automatically handles null values and delimiters

Performance Comparison and Selection Recommendations

In practical applications, appropriate methods should be selected based on specific scenarios:

Technical Details and Considerations

When implementing field value concatenation, the following technical details require attention:

1. Data Type Processing

When concatenating non-string type fields, explicit type conversion is mandatory:

-- Incorrect example: directly concatenating numeric types
DECLARE @result VARCHAR(100)
SELECT @result = @result + CAST(NumericColumn AS VARCHAR(10)) + ', '
FROM SomeTable

2. Result Length Limitations

Variable lengths should be reasonably set according to expected result sizes to avoid truncation:

-- Use MAX length to avoid truncation
DECLARE @result VARCHAR(MAX)
SET @result = ''
SELECT @result = @result + ModuleValue + ', '
FROM LargeTable

3. Null Value Handling

Use ISNULL or COALESCE to handle potentially null fields:

SELECT @result = @result + ISNULL(ModuleValue, 'N/A') + ', '
FROM Table_X
WHERE ModuleID = @ModuleID

4. Sorting Control

If specific concatenation order is required, ORDER BY must be explicitly specified:

DECLARE @result VARCHAR(500)
SET @result = ''
SELECT @result = @result + ModuleValue + ', '
FROM TableX
WHERE ModuleId = @ModuleId
ORDER BY SomeOrderColumn

Practical Application Case

The following is a complete stored procedure example demonstrating the application of field value concatenation in actual projects:

CREATE PROCEDURE GetModuleValuesConcatenated
    @ModuleID INT,
    @Delimiter VARCHAR(10) = ', '
AS
BEGIN
    DECLARE @Result VARCHAR(MAX)
    SET @Result = ''
    
    SELECT @Result = @Result + ModuleValue + @Delimiter
    FROM Table_X
    WHERE ModuleID = @ModuleID
    ORDER BY ModuleValue
    
    -- Remove trailing excess delimiter
    IF LEN(@Result) > 0
        SET @Result = LEFT(@Result, LEN(@Result) - LEN(@Delimiter))
    
    SELECT @Result AS ConcatenatedValues
END

Conclusion

Field value concatenation is a common requirement in SQL Server database development. This paper systematically introduces multiple implementation methods. The variable assignment concatenation method remains the preferred solution for most scenarios due to its excellent performance and good compatibility. For SQL Server 2017 and later versions, the STRING_AGG function provides a more concise and modern solution. In practical applications, developers should select the most appropriate implementation based on project requirements, database versions, and performance considerations, while paying attention to key technical details such as data type conversion, null value handling, and result length management.