Home > Database > Mysql Tutorial > How to Combine Multiple SQL Server Rows into a Single Comma-Delimited Column?

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

Barbara Streisand
Release: 2025-01-21 07:02:36
Original
986 people have browsed it

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>
Copy after login

Convert to:

<code>[TicketID]  [People]
T0001       Alice, Bob
T0002       Catherine, Doug
T0003       Elaine</code>
Copy after login

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>
Copy after login
  1. The FOR XML PATH clause groups values ​​and returns them as XML strings.
  2. The
  3. TYPE clause specifies that the result should be a text string.
  4. The
  5. value function extracts text from an XML string.
  6. The ISNULL function handles the case where there are no values ​​to combine.
  7. 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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template