In SQL Server, you can use the COALESCE() and XML PATH functions to concatenate text from multiple rows into a single text string.
When retrieving data from the database, you may need to concatenate data from multiple rows into a single text string. Currently, you can use methods like the STUFF function with the XML PATH, GROUP BY clause, and SQL Concat(). In this blog, let’s explore the different approaches to concatenating text from multiple rows into a single text string in SQL Server.
Table of Contents:
Methods to Concatenate Multiple Rows into a Single Line of String in SQL Server
First, we will create a dataset, which we will use throughout this article. Then, we will incorporate these methods to achieve the desired output.
Example:
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Department NVARCHAR(50),
EmployeeName NVARCHAR(100)
);
INSERT INTO Employees (ID, Department, EmployeeName)
VALUES
(1, 'HR', 'Kani'),
(2, 'Content Writer', 'Vasu'),
(3, 'HR', 'Ravi'),
(4, 'HR', 'Sri'),
(5, 'Content Writer', 'Rathore');
Method 1: Using the Coalesce Function in SQL Server
When concatenating rows into text strings, the COALESCE() function in SQL Server returns the first non-null value from the list. The Coalesce function will avoid null strings, as it may break the concatenation.
Example:
DECLARE @EmployeeList NVARCHAR(MAX);
SET @EmployeeList = '';
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + EmployeeName
FROM Employees
WHERE Department = 'HR';
SELECT @EmployeeList AS EmployeeNames;
Output:
Explanation: The Coalesce function retrieves all names of the employees from the department “HR” in a list.
Using the Coalesce Function in SQL Concat() Function
To join one or more strings in SQL Server, we can use the SQL Concat() function. The SQL Concat() will use the Coalesce function to handle NULL values effectively by considering them as an empty string. It can function effectively row-wise and cannot concatenate multiple rows into a single string.
Example:
SELECT EmployeeName, Department,
CONCAT(COALESCE(EmployeeName, ''), ' works in ', COALESCE(Department, ''), ' department.') AS EmployeeDetails
FROM Employees;
Output:
Explanation: A fixed string, ‘works in,’ is added to make the sentence more readable.
Method 2: Using XML PATH Function in SQL Server
The XML PATH in SQL Server treats the output as an XML fragment and allows it to merge values efficiently. The XML PATH can handle NULL values and combine multiple columns into a single line of text.
Example:
SELECT SUBSTRING((
SELECT ', ' + EmployeeName + ' - ' + Department
FROM Employees
FOR XML PATH('')), 3, 1000) AS [Employee Details];
Output:
Explanation: The XML PATH function retrieves all the data from all the departments and concatenates it in a single line.
Method 3: Using the STUFF Function in SQL Server
The STUFF function in SQL Server uses two methods to concatenate the multiple rows into a single text string.
Using the STUFF Function with XML PATH
The STUFF function with XML PATH will concatenate multiple rows from the dataset
without grouping them into their respective departments.
Example:
SELECT STUFF((
SELECT ', ' + EmployeeName
FROM Employees
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS EmployeeNames;
Output:
Explanation: The STUFF with XML PATH will print the output of all the names without grouping them by their department.
Using the STUFF Function with the group-by Clause
The STUFF function with a group-by clause in SQL Server will retrieve all the data with the respective department.
Example:
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.
Method 4: Using STRING_AGG() Function in SQL Server
The STRING_AGG() function in SQL Server is used to concatenate multiple rows into a single text string. The STRING_AGG() function is only available in SQL Server 2017+. The STRING_AGG() uses the separators between the concatenated strings.
Example:
SELECT STRING_AGG(EmployeeName, ', ') AS EmployeeNames
FROM Employees;
Output:
Explanation: The STRING_AGG() function will take the EmployeeName from each row in the Employees table and concatenate them into a single string. Each EmployeeName is separated by separators (,).
Comparing all the Methods
| Methods | Handling NULL values | Performance | Accessibility |
| Coalesce function | Does not handle NULL values. | It works efficiently for smaller datasets. | Row-wise processing is slower for large datasets. |
| XML PATH | Can handle NULL values. | Can handle multiple rows and large datasets. | Using XML-based concatenation is better for medium datasets. |
| STUFF function with XML PATH and group-by-clause | Can handle Null values. | Can handle multiple rows and give output using group-by-clause. | Similar to XML PATH but provides better control over formatting. |
| STRING_AGG() | It handles NULL values automatically. | Uses delimiters to separate strings. | Designed for row-wise concatenation, not for grouping multiple rows. |
Real-world Use Cases
Example 1: Creating a list of employee’s names and grouping them by their department.
SELECT Department, STRING_AGG(EmployeeName, ', ') AS EmployeeNames
FROM Employees
GROUP BY Department;
Output:
Example 2: To create a list using separators for order invoices.
SELECT Department, STRING_AGG(EmployeeName, ', ') AS Employees
FROM Employees
GROUP BY Department;
Output:
Error Handling and Edge Cases
Case 1: Handling NULL Values in Concatenation:
During the retrieval of data, if the name or department column is NULL, which means there is no entry of data, the result will also be NULL.
Example:
SELECT Department, STRING_AGG(EmployeeName, ', ') AS Employees
FROM EmployeeInfo
GROUP BY Department;
Output:
Explanation: Here the STRING_AGG checks if EmployeeName or the department is NULL and if it does not have any input value given, it states ‘null’ in the output table
Case 2: Using ‘Unknown’’ instead of ‘NULL’ in empty records
SELECT Department, STRING_AGG(ISNULL(EmployeeName, 'Unknown'), ', ') AS Employees
FROM EmployeeInfo
GROUP BY Department;
Output:
Explanation: The ISNULL() function checks if EmployeeName or the department is NULL and if it does not have any input value given, it states ‘Unknown’ in the output table
Alternative Approaches
Using CrossApply with FOR JSON PATH helps to avoid recursion errors. This method allows you to work with data more efficiently without running into issues that occur with recursion
Example:
SELECT DISTINCT Department,
(SELECT EmployeeName
FROM Employees e
WHERE e.Department = d.Department
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS Employees
FROM Employees d;
Output:
Conclusion
The coalesce handles simple concatenation in a single column, while the XML PATH effectively handles multiple-row concatenation. Using the XML PATH, the STUFF() function gives output without grouping, and a GROUP BY clause is useful for grouping multiple rows. Understanding these approaches helps you effectively concatenate text from multiple rows into a single text string in SQL Server.