How to Concatenate Multiple Rows into a Single Comma-Separated Column in SQL Server?

Combining Multiple SQL Server Rows into a Single Comma-Separated Column

This guide demonstrates how to merge multiple rows into a single column with comma-separated values within SQL Server. Let's use this example dataset:

<code class="language-sql">[TicketID], [Person]
T0001       Alice
T0001       Bob
T0002       Catherine
T0002       Doug
T0003       Elaine</code>

Our goal is to achieve this output:

<code class="language-sql">[TicketID], [People]
T0001       Alice, Bob
T0002       Catherine, Doug
T0003       Elaine</code>

Solution (SQL Server 2005 and later):

The following SQL query leverages the STUFF function to efficiently concatenate the values:

<code class="language-sql">SELECT t.TicketID,
       STUFF(ISNULL((SELECT ', ' + x.Person
                FROM @Tickets x
               WHERE x.TicketID = t.TicketID
            GROUP BY x.Person
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') AS People
  FROM @Tickets t
GROUP BY t.TicketID;</code>

Explanation:

• This solution is compatible with SQL Server 2005 and later versions.
• The STUFF function replaces a portion of a string with another string. Here, it removes the leading comma.
• The FOR XML PATH clause constructs an XML representation of the data, which is then converted to a comma-separated string using .value('.','VARCHAR(max)'). This is a common technique for string aggregation in SQL Server.
• ISNULL handles cases where a TicketID has only one associated person, preventing errors.

This method provides a concise and effective solution for concatenating multiple rows into a single comma-separated column in SQL Server. Remember to replace @Tickets with your actual table name.

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