Write a SQL query to concatenate multiple rows into a single string?

USE TestDB;
GO

DROP TABLE IF EXISTS dbo.product_category;
GO

CREATE TABLE dbo.product_category (
    product_category_id INT NOT NULL PRIMARY KEY,
    product_category_name VARCHAR(50)
)
GO

INSERT INTO dbo.product_category VALUES (1,'Bikes');
INSERT INTO dbo.product_category VALUES (2,'Components');
INSERT INTO dbo.product_category VALUES (3,NULL);
INSERT INTO dbo.product_category VALUES (4,'Accessories');
INSERT INTO dbo.product_category VALUES (5,'Clothing');
GO

SELECT * FROM dbo.product_category;

Solution

This can be implemented in different ways.

1. STUFF function with XML path

   We are concatenating comma with the product category name and then converting the products as single string using xml path. Then replace the leading comma with blank using stuff function.

2. Substring function with XML path

   This is similar to the previous appraoch. But instead of replacing the leading comma, we are you using substring function to select all the text except the leading comma. We have given approximate length as 10000 and this can be modified based on the scenario.

3. STRING_AGG function

   This string_agg function was introduced in SQL Server 2017. It concatenates the values of string and places separator(comma in our case) values between them. The separator isn't added at the end of string. If there are NULL, then it will be ignored and separator will not be added for that.

4. COALESCE function

   This is an old way of concatenating the rows into a string. If there is a NULL, the output will NULL as well. We should either filter out the NULL or replace a NULL with other string value. In the solution below we have replaced with a blank value and this is the reason there is two commas in the result.

-- #1 using stuff function and xml path
SELECT STUFF(
    (SELECT ',' + product_category_name
    FROM dbo.product_category
    FOR XML PATH ('')),1,1,'')

-- #2 using substring function and xml path
SELECT SUBSTRING(
    (SELECT ',' + product_category_name
    FROM dbo.product_category
    FOR XML PATH ('')),2,10000)

-- #3 using string_agg function
SELECT STRING_AGG(product_category_name,',')
FROM dbo.product_category 

-- #4 using coalesce when there is null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + COALESCE(product_category_name,'')
FROM dbo.product_category
SELECT @concat_string;
GO

-- #4 using coalesce when there is not any null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + product_category_name
FROM dbo.product_category 
WHERE product_category_name IS NOT NULL
SELECT @concat_string;
GO

Output: