How to Concatenate Multiple SQL Rows into a Single Comma-Delimited Column?

SQL Techniques for Combining Multiple Rows into a Single Comma-Delimited Field

Aggregating multiple rows of data into a single column, separated by commas, is a frequent requirement in data manipulation. This guide demonstrates how to achieve this concatenation using SQL, focusing on Microsoft SQL Server 2005 and later.

Here's a SQL Server solution:

<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 ConcatenatedPersons
  FROM @Tickets t
GROUP BY t.TicketID</code>

This approach employs the STUFF function to efficiently remove a leading comma from the concatenated string. The nested query leverages FOR XML PATH to create a comma-separated list from the individual Person values associated with each TicketID. The ISNULL function handles cases where no persons are associated with a given ticket ID. The result is a single column (ConcatenatedPersons) containing the comma-separated list of persons for each ticket. This method provides a clean and efficient way to achieve the desired concatenation within SQL Server. Adapt this technique to your specific table and column names for optimal results.

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