How Can I Concatenate Text Data from Multiple Rows in SQL Server?

Combining Text Data from Multiple SQL Server Rows: A Comprehensive Guide

Many SQL Server users need to combine text data across multiple rows into a single string. This is invaluable for report generation, data merging, and string-based calculations.

Imagine a table with a list of names:

<code>Peter
Paul
Mary</code>

The goal is to create a comma-separated string: Peter, Paul, Mary

Method 1: For SQL Server 2017 and Later (STRING_AGG)

SQL Server 2017 and Azure SQL Database offer the efficient STRING_AGG function:

<code class="language-sql">SELECT STRING_AGG(Name, ', ') AS ConcatenatedNames
FROM TableName;</code>

Method 2: XML Parsing (For Older SQL Server Versions)

For earlier SQL Server versions, XML parsing provides a solution:

<code class="language-sql">SELECT
    LEFT(
        (
            SELECT Name + ',' AS [text()]
            FROM TableName
            ORDER BY Name
            FOR XML PATH('')
        ).value('.', 'nvarchar(max)'),
        LEN((
            SELECT Name + ',' AS [text()]
            FROM TableName
            ORDER BY Name
            FOR XML PATH('')
        ).value('.', 'nvarchar(max)')) - 1
    ) AS ConcatenatedNames
FROM TableName;</code>

Method 3: Using the STUFF Function

The STUFF function offers another approach:

<code class="language-sql">SELECT
    STUFF(
        (
            SELECT ',' + Name
            FROM TableName
            ORDER BY Name
            FOR XML PATH('')
        ).value('.', 'nvarchar(max)'),
        1, 1, ''
    ) AS ConcatenatedNames
FROM TableName;</code>

These methods efficiently concatenate text data from multiple rows within SQL Server, simplifying data manipulation and aggregation. Choose the method appropriate for your SQL Server version.

The above is the detailed content of How Can I Concatenate Text Data from Multiple Rows in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!