How to Concatenate Text from Multiple Rows into a Single String in SQL Server?

SQL Server merge multiple lines of text into a single line string

In various database scenarios, you may need to combine string values ​​from multiple rows into a single, coherent string. This is especially useful when you need to combine information from different data points into a comprehensive summary or display.

Problem Statement

Consider a table called "Names" that contains the following three rows:

• Peter
• Paul
• Mary

Challenge

The goal is to convert these individual lines into a single comma-separated string: "Peter, Paul, Mary".

Solution

In SQL Server 2017 and Azure SQL Database, you can use the STRING_AGG() function to accomplish this task. The syntax of STRING_AGG() is as follows:

<code class="language-sql">STRING_AGG(<expression> [ , delimiter ] [ ORDER BY <expression> [ ASC | DESC ] ])</code>

For our scenario, the expression will be the name column and the delimiter will be a comma. The ORDER BY clause ensures that names appear in a specific order (that is, alphabetical order).

SQL Query

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

Output

<code>ConcatenatedNames
---------------------
Peter, Paul, Mary</code>

Alternative solutions for earlier SQL Server versions

If you are using an earlier version of SQL Server, such as SQL Server 2005, you can use the following method:

<code class="language-sql">SELECT
    (
        SELECT StudentName + ',' AS [text()]
        FROM dbo.Students
        WHERE SubjectID = Main.SubjectID
        ORDER BY StudentID
        FOR XML PATH('')
    ).value('text()[1]', 'nvarchar(max)') AS Students
FROM
    (
        SELECT DISTINCT SubjectID
        FROM dbo.Students
    ) AS Main;</code>

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