Logo
Back

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

Combining text from multiple rows into a single string is a common task in SQL, especially when you need to generate reports or produce user-friendly outputs. This guide will demonstrate two popular methods: one using STRING_AGG() (available from SQL Server 2017 onwards) and another using STUFF with FOR XML PATH (for older versions).

1. Using STRING_AGG() (SQL Server 2017+)

Microsoft introduced STRING_AGG() in SQL Server 2017 to simplify row concatenation. The function accepts two arguments: the column to concatenate and the separator string.

SELECT 
    STRING_AGG(ColumnToConcatenate, ', ') AS concatenated_result
FROM YourTable;

1.1 Example

Imagine a table Tags with a single column called TagName:

-- Tags Table
TagName
--------
SQL
Database
Performance
Indexing

To combine all tag names into a single comma-separated string:

SELECT 
    STRING_AGG(TagName, ', ') AS AllTags
FROM Tags;

Result: SQL, Database, Performance, Indexing

1.2 Why Use STRING_AGG?

  1. Simplicity: Far more readable than the older XML-based approach.
  2. Optional WITHIN GROUP (ORDER BY): You can control the order of concatenation.
SELECT 
    STRING_AGG(TagName, ', ') WITHIN GROUP (ORDER BY TagName) AS AllTags
FROM Tags;

2. Using STUFF and FOR XML PATH (Pre-SQL Server 2017)

If you’re on an older SQL Server version, you can still achieve row concatenation using FOR XML PATH and the STUFF function.

SELECT
    STUFF((
        SELECT ', ' + t2.TagName
        FROM Tags t2
        FOR XML PATH('')
    ), 1, 2, '') AS AllTags;

2.1 Breaking Down the Query

  1. FOR XML PATH(''): Generates a comma-separated list by concatenating each row’s TagName prefixed with , .
  2. STUFF(): Removes the leading , (first two characters) from the resulting string.

Result: SQL, Database, Performance, Indexing

2.2 Pros and Cons

  • Pros: Works on SQL Server versions prior to 2017.
  • Cons: Less intuitive and more verbose compared to STRING_AGG().

3. Best Practices

  1. Order Matters

    • If you need a specific order, use STRING_AGG() with ORDER BY or wrap the FOR XML approach in a subquery that orders rows.

  2. Performance Considerations

    • For large result sets, concatenation can become a bottleneck. Test and index appropriately.
    • If your output can get extremely large, consider data type limits (e.g., VARCHAR(MAX)).

  3. Null Handling

    • Be aware that NULL values can be skipped or appear as 'NULL' in some approaches. Check your actual data and needs.

  4. Length Limits

    • STRING_AGG() returns a VARCHAR(MAX) or NVARCHAR(MAX) if the column being aggregated is of a max type. For extremely large outputs, watch for potential truncation issues in your application layer.

4. Take Your SQL Skills to the Next Level

If you want to deepen your understanding of SQL for technical interviews or real-world projects, consider these resources from DesignGurus.io:

5. Preparing for Interviews or Real-World Scenarios

Conclusion

Concatenating text from multiple rows into a single string is straightforward in SQL Server—especially since the introduction of STRING_AGG(). For older SQL Server versions, you can achieve the same results with FOR XML PATH and STUFF. By understanding both methods, you’ll be equipped to handle a wide range of environments and data scenarios.

For more in-depth SQL knowledge, database design insights, and interview preparation, explore the courses on DesignGurus.io. This know-how will serve you well in both technical interviews and production database management. Good luck and happy querying!

CONTRIBUTOR
TechGrind