The STUFF() function in SQL Server uses two methods, which are XML PATH and XML with the GROUP BY function, to get the multiple rows of text into a single-line string. It is usually used for modifying the text dynamically and for string manipulation.
Table of Contents:
What is the STUFF() Function in SQL Server?
The STUFF() function performs with the XML PATH in SQL Server to concatenate text from multiple rows into a single text string. STUFF() is used to remove the leading delimiters in the string. XML PATH will convert the data into XML fragments. After converting it, this will change the XML fragment into strings.
Example for STUFF() Function in SQL Server
The STUFF() function with XML PATH combines the values from different rows together and groups them based on their categories.
Example: To get the products with their ID and category
-- Create the Product table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Category NVARCHAR(50),
ProductName NVARCHAR(100)
);
-- Insert product data
INSERT INTO Products (ProductID, Category, ProductName)
VALUES
(1, 'Electronics', 'Smartphone'),
(2, 'Electronics', 'Laptop'),
(3, 'Electronics', 'Tablet'),
(4, 'Furniture', 'Sofa'),
(5, 'Furniture', 'Dining Table'),
(6, 'Furniture', 'Chair'),
(7, 'Clothing', 'T-Shirt'),
(8, 'Clothing', 'Jeans');
SET QUOTED_IDENTIFIER ON;
SELECT Category,
STUFF((
SELECT ', ' + ProductName
FROM Products P
WHERE P.Category = C.Category
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ProductList
FROM Products C
GROUP BY Category;
Output:
Explanation: The XML PATH converts all the data into XML format, then converts them into strings. Then using GROUP BY, it segregated the data based on category.
Using the STUFF Function with the group-by Clause
The STUFF() function with the GROUP BY clause in SQL Server will retrieve the data from multiple rows into a single string text. STUFF() will eliminate the excess delimiter from a string of text, and GROUP BY will group the string based on its category.
Example:
-------Create a table named employees
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Department NVARCHAR(50),
EmployeeName NVARCHAR(100)
);
------Insert the value into employees table
INSERT INTO Employees (ID, Department, EmployeeName)
VALUES
(1, 'HR', 'Kani'),
(2, 'Content Writer', 'Vasu'),
(3, 'HR', 'Ravi'),
(4, 'HR', 'Sri'),
(5, 'Content Writer', 'Rathore');
SET QUOTED_IDENTIFIER ON;
SELECT Department,
STUFF((SELECT ', ' + EmployeeName
FROM Employees e2
WHERE e1.Department = e2.Department
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS EmployeeNames
FROM Employees e1
GROUP BY Department;
Output:
Explanation: The XML PATH, together with the group-by clause, will give the output and divide it based on their department.
Conclusion
The STUFF() function is one of the methods to concatenate multiple rows of text into a single string. To make it more efficient, it combines with functions like GROUP BY and XML PATH. The aggregate function GROUP BY is helpful to group the data based on the category of the string. This method makes sure that concatenated strings are grouped correctly with the help of the GROUP BY function.
Some Other Methods to Concatenate Text from Multiple Rows into a Single Text String