How to Concatenate SQL Server Database Rows into a Single Comma-Separated String?

Combining SQL Server Rows into a Single Comma-Separated String

Microsoft SQL Server offers an efficient method to concatenate rows from a table into a single string separated by commas. This is achieved using a combination of the STUFF and FOR XML functions.

Example:

Let's create a sample table and populate it with data:

<code class="language-sql">DECLARE @MyTable TABLE (
    ItemName VARCHAR(20)
);

INSERT INTO @MyTable (ItemName) VALUES
('Apple'),
('Banana'),
('Orange'),
('Grape'),
('Mango');</code>

Now, execute the following query to concatenate the ItemName column values into a single comma-separated string:

<code class="language-sql">SELECT STUFF((
    SELECT ',' + ItemName
    FROM @MyTable
    FOR XML PATH('')
), 1, 1, '') AS ConcatenatedItems;</code>

Output:

The query will produce the following result:

<code>ConcatenatedItems
Apple,Banana,Orange,Grape,Mango</code>

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