How to Convert SQL Server Table Rows into a Comma-Delimited String?

Convert table rows to comma separated strings in SQL Server

To concatenate rows into a comma-separated string in SQL Server, you can use the STUFF() and FOR XML PATH() functions together.

The demonstration is as follows:

1. Create and populate the sample table:

   <code class="language-sql"> DECLARE @T AS TABLE
    (
        Name varchar(10)
    )
   
    INSERT INTO @T VALUES
    ('John'),
    ('Vicky'),
    ('Sham'),
    ('Anjli'),
    ('Manish')</code>

2. Concatenate lines using comma separators:

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

This query will concatenate the rows in @T into a single comma-separated string, resulting in the following:

<code>输出
John,Vicky,Sham,Anjli,Manish</code>

Instructions:

The
• FOR XML PATH() function generates an XML representation of the lines, each contained in a comma-separated list.
• The STUFF() function is then used to remove redundant opening and closing angle brackets from the result.
• The 1, 1 in the STUFF() function indicates that the first character (i.e. the leading comma) should be removed.

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