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:
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:
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.
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.