How to Concatenate Rows with a Comma Delimiter in SQL Server?

Join rows using comma delimiter in SQL Server

This article explains how to merge multiple rows of data into a comma-separated string in SQL Server. In MS SQL Server, this can be effectively achieved by using a combination of STUFF and FOR XML functions.

Here is a sample table (@T):

<code class="language-sql">DECLARE @T AS TABLE
(
    Name varchar(10)
)

INSERT INTO @T VALUES
('John'),
('Vicky'),
('Sham'),
('Anjli'),
('Manish')</code>

To concatenate the names into a comma-separated string, execute the following query:

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

This query utilizes FOR XML to generate an XML hierarchy of names, and then utilizes the STUFF function to construct the final string. The output will be:

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

This method effectively concatenates multiple rows of data into a single string, making it ideal for tasks such as generating comma-separated lists or exporting data in a specific format.

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