How to Concatenate Text From Multiple Rows in SQL Server

When we fetch data from a table, there may be requirements to concatenate the text value of a table column in multiple rows into a single row. There are many ways we can concatenate multiple rows into single row SQL Server. We can use different ways based on need and convenience.

In this article, we will discuss how to concatenate text from multiple rows into a single text string in SQL Server using various methods which are COALESCE Function, XML PATH Function, and STUFF Function with XML PATH and Group By Clause.

Concatenating Text Columns in SQL Server

There can be many approaches to concatenate text from multiple rows into a single text string in SQL Server. Generally, there are 3 approaches used to concatenate text columns as below:

1. COALESCE Function
2. XML PATH method
3. STUFF Function with XML PATH and Group By Clause

We will discuss these methods in detail below with examples. We are using two tables namely Categories and Products, and inserting some sample data to explain the 3 methods to concatenate text data.

Ways to Concatenate Text From Multiple Rows Into a Single Text

To understand How to concatenate text from multiple rows into a single text string in SQL Server, We need two tables on which we will perform various operations and queries. So here we will create two table called Categories and Products table. Also, we will insert some data into it.

Create Categories Table:

CREATE TABLE [dbo].[Categories](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryId] [int] NULL,
    [CategoryName] [varchar](100) NULL
) ON [PRIMARY]
GO

Insert data to Categories Table:

Insert into Categories Values  (1,'Groceries')
Insert into Categories Values  (2,'Spices')
Insert into Categories Values  (7,'Rice')
Insert into Categories Values  (8,'Cerials')
Insert into Categories Values  (6,'Noodles')

Create Products Table:

CREATE TABLE [dbo].[Products](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](100) NULL,
    [SupplierID] [tinyint] NULL,
    [CategoryID] [tinyint] NULL,
    [Unit] [varchar](100) NULL,
    [Price] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

Insert data into Products Table:

Insert into Products Values ('Chais',    1,    1,    'boxes x 20 bags',    1800)
Insert into Products Values ('Chang Gin',    1,    1,    '24 - 12 oz bottles',    1900)
Insert into Products Values ('Hokka Chian',    1,    1,    '12 Packets',    1400)
Insert into Products Values ('Mongan Cho  Bons',    1,    1,    '12 Packets',    1600)
Insert into Products Values ('Aniseed Syrup',    1,    1,    '12 - 550 ml bottles',    1500)
Insert into Products Values ('Chef Anton Cajun Seasoning',    1,    1,    '48 - 6 oz jars',    2400)
Insert into Products Values ('Chef Anton Gumbo Mix',    3,    2,    '36 boxes',    2100)
Insert into Products Values ('Grandma Boysenberry Spread',    3,    2,    '12 - 8 oz jars    ',250)
Insert into Products Values ('Uncle Bob Organic Dried Pears',    3,    7,    '12 - 1 lb pkgs.',    30)
Insert into Products Values ('Northwoods Cranberry Sauce',4    ,2    ,'12 - 12 oz jars',    400)
Insert into Products Values ('Mishi Kobe Niku',    4,    6,    '18 - 500 g pkgs.',    970)
Insert into Products Values ('Jeera Rice',    1,    7,    '10 kg'    ,1200)
Insert into Products Values ('Oats Quaker',    2,    8,    '1 kg',    490)
Insert into Products Values ('Matta Rice',    1,    7,'5 kg',    275)
Insert into Products Values ('Briyani Rice - India Gate',    1,    7,    '5 kg',    600)
Insert into Products Values ('Mohans Cornflake',    2,    8,    '1 kg',    300)

Method 1: Using COALESCE Function

COALESCE is a string function that returns NON NULL values and is used to handle string manipulation, concatenation, and pivoting operations. In our current example, we are using this COALESCE function to Concatenate string data from multiple rows into a single row and also this helps to remove any NULL values while concatenation the string values.
The text values from each row is separated by a comma (,) or any other separater between 2 text values.

The below example demonstrates this concatenation of strings as below.

Query:

DECLARE @Product_Names VARCHAR(MAX);
SELECT @Product_Names = COALESCE(@Product_Names + ',' + ProductName,ProductName)
FROM Products;
SELECT @Product_Names AS 'Name of all Products';

Output:

Example using COALESCE

Explanation: In the above example using COALESCE function and string concatenation method with a string variable the concatenated text data os combined and stored in the @Product_Names string variable. When we use Select statement to display the final concatenated text, below result will be displayed.

In the above output the ProductNames from Products table are concatenated and displayed as single text string. Each product name is separated by a comma (‘).

Method 2: Using XML PATH Function

XML PATH function is another method to conctenate strings from a column in multiple rows to a single row. The XML PATH generally returns the result with XML elements, but it can be removed by passing a empty ” in XML PATH as XML PATH (”).

In this example below we are concatenating the text values in ‘ProductName‘ column from Products table using comma (,) separater. Also with the ProductName, the ProductID value to show the productid value for each product, but all combined as single string from all the records returned by the query.

Query:

SELECT SUBSTRING(
    (
        SELECT ',' + Convert(varchar(100),ProductID) + '.' + ProductName AS [text()]
        FROM Products
        ORDER BY ProductName
        FOR XML PATH('')
    ), 2, 1000) AS 'Name of all Products';

Output:

Example using XML PATH

Explanation: In the above example we are using XML PATH (”) function to concatenate text values from multiple rows into a single string separated by a comma (,). Also in this example we are combining the ProductID with each ProductName. To remove the first comma in the string the SUBSTRING function is also used, so that first comma can be removed. The result can been seen as below:

In the above output we can see the concatenated Product names from multiple rows as a single text row. Each product name has the product id prefixed.

Method 3: Using STUFF Function with XML PATH and Group By Clause

There could be requirement to concatenate product names based on categories they belong. This can be done using STUFF function in combination with XML PATH and adding the GROUP BY Clause for the SELECT query to fetch Product Names by category id from Product table in our example below.

Query:

SELECT CategoryID,  
ProductName=STUFF  
(  
    (  
      SELECT DISTINCT ', '+ CAST(g.ProductName AS VARCHAR(MAX))  
      FROM Products g,Categories e   
      WHERE g.CategoryId=e.CategoryId and e.CategoryId=t1.CategoryID   
      FOR XML PATH('')  
    ),1,1,''  
)  
FROM Products t1  
GROUP BY CategoryID  

Output:

Example using XML PATH , STUFF and GROUP BY

Explanation: In the above exmaple we are concatenating the Text values from multiple columns but grouping the product names by the categories each product name belongs. So here again the XMAL PATH is used with STUFF string functions and GROUP BY categoryId is used to combine the names categorywise.

In the above example the the Product names are cocatenated using the XML and product name is grouped by categories. There are 5 categories in the above example and the product names are combined by category of each product. so categorywise the concatenated product names along with category id on the left column is displayed.

Conclusion

In this article we have seen how to concatenate strings from multiple rows from the result of a SQL SELECT statement into a single row. We used three methods to concatenate string like COALESCE, XML PATH and XML PATH with STUFF including GROUP BY. These are not direct functions for string concatenations, but methods with combination of other string functions as shown in the examples used in this article.