Merge multiple rows into one column of comma separated values in SQL Server
Question:
You need to merge multiple rows with common identifiers into a single column, where the values are separated by commas. For example, you want to get data from:
<code>[TicketID] [Person] T0001 Alice T0001 Bob T0002 Catherine T0002 Doug T0003 Elaine</code>
Convert to:
<code>[TicketID] [People] T0001 Alice, Bob T0002 Catherine, Doug T0003 Elaine</code>
Solution for SQL Server 2005:
<code class="language-sql">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, '')</code>
Example query:
<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, '') [无前导逗号], ISNULL((SELECT ', ' + x.Person FROM @Tickets x WHERE x.TicketID = t.TicketID GROUP BY x.Person FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), '') [如果非空则有前导逗号] FROM @Tickets t GROUP BY t.TicketID</code>
The above is the detailed content of How to Combine Multiple SQL Server Rows into a Single Comma-Delimited Column?. For more information, please follow other related articles on the PHP Chinese website!