Home >Database >Mysql Tutorial >How to Concatenate Rows into a Comma-Delimited String in Microsoft SQL Server?

How to Concatenate Rows into a Comma-Delimited String in Microsoft SQL Server?

Patricia Arquette
Patricia ArquetteOriginal
2025-01-16 23:37:12908browse

How to Concatenate Rows into a Comma-Delimited String in Microsoft SQL Server?

Concatenate lines to comma separated string

A common task for database users using Microsoft SQL Server is to merge multiple rows into a single comma-separated string. This simplifies data manipulation and allows straightforward processing.

Question:

How can we retrieve a comma separated string from a set of rows using Microsoft SQL Server?

Solution:

Microsoft SQL Server provides two powerful methods for this purpose: STUFF and FOR XML. The following query demonstrates how to use them effectively:

<code class="language-sql">SELECT STUFF((
    SELECT ',' + Name
    FROM @T
    FOR XML PATH('')
), 1, 1, '') AS [output];</code>

Explanation:

  • STUFF: The STUFF() function inserts the specified string into another string at the given position.
  • FOR XML PATH (''): The FOR XML PATH ('') clause creates an XML representation of the data, where each row is represented as an element. The empty string parameter ensures that no XML markup is generated, making it suitable for creating comma-separated lists.

By combining these techniques, the query progressively concatenates names with commas, resulting in a single comma-separated string.

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

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