How to Concatenate Rows with Commas in MSSQL Server?

Use comma to join rows in MSSQL Server

Querying and merging multiple rows into a single comma separated string in MSSQL Server can be achieved using a combination of STUFF and FOR XML functions. The specific method is as follows:

Create a sample table and populate the values:

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

Construct queries using STUFF and FOR XML:

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

This query concatenates the Name column of all rows in table @T into a single string. It uses FOR XML to convert the table into an XML representation, and then uses STUFF to concatenate the XML nodes (Name elements, with " " as delimiter) into a single string. The result is similar to:

<code>output
John,Vicky,Sham,Anjli,Manish</code>

The above is the detailed content of How to Concatenate Rows with Commas in MSSQL Server?. For more information, please follow other related articles on the PHP Chinese website!