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
- Using STRING_AGG() in SQL Server
- Using COALESCE() Function in SQL Server
- Using FOR XML PATH clause in SQL Server
- Using CONCAT() function in SQL Server
Explore key concepts in SQL Server, Oracle SQL, and web development and programming through the articles below.
Convert rows to columns using PIVOT in SQL Server – Transform row data into columns using PIVOT in SQL Server.
FOR XML PATH clause in SQL Server – Using FOR XML PATH to concatenate rows in SQL Server.
Create ID with AUTO INCREMENT on Oracle – Create sequential IDs in Oracle with AUTO INCREMENT.
Sequence and Trigger for AUTO INCREMENT ID in Oracle – Create auto-increment IDs with Oracle triggers and sequences.
GUID for AUTO INCREMENT ID in Oracle – How to use SYS_GUID for unique ID generation in Oracle.
How to remove a specific item from an array in JavaScript – Delete a specific item from an array in JavaScript.
CSS position absolute to horizontally center element – Use transform and absolute position to center div in CSS.
How to align Flexbox elements left, right, and center using CSS – Align Flexbox elements to left, right, and center with CSS.