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

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:

1. Use the STUFF function to combine values ​​into a single string:

<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>

2. The FOR XML PATH clause groups values ​​and returns them as XML strings.
The
3. TYPE clause specifies that the result should be a text string.
The
4. value function extracts text from an XML string.
5. The ISNULL function handles the case where there are no values ​​to combine.
6. The final STUFF function removes leading commas from the combined string, if present.

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!