This article gives an explanation about an efficient way to concatenate value from multiple rows to a single string in the SQL server. There are many multiple ways are available in the SQL server to concatenate value from multiple rows to a single string such as using Substring, FTUFF function, XML node, STRING_AGG, and many others, but to improve the performance of our query we have to choose an efficient and easy way to concatenate multiple rows within a single row in SQL Server.
In my previous article, I have explained how to concatenate text from multiple rows into a single text string in SQL server using STUFF() function with syntax and example as well as how to split comma separated value in SQL Server and how to PIVOT and UNPIVOT column in SQL Server with example and how to find the last date of any month in SQL Server that you might like to read.
I have seen, many developers still using older ways to concatenate value from multiple rows to a single string, they still using either using substring or STUFF function or XML node but you have to choose a more efficient and easy way to archive this kind of requirement, I also have written an article about the use of STUFF function where I have explained how you can concatenate value from multiple rows to a single string with XML PATH using STUFF function in the SQL server, In this article, I'll give an explanation about one another SQL server function STRING_AGG that is supported in the SQL Server version 2017 and later.
Requirement
1) Explain what is STRING_AGG function in the SQL server.
2) Create a sample table with the column DepartmentName and EmployeeName.
3) Insert some dummy records on the table for demonstration.
4) List all the department wise employee names.
What is the STRING_AGG function in the SQL server?
The STRING_AGG() function is an aggregate function supported in the SQL Server version 2017 and later. This function is used for concatenates values.
Syntex
STRING_AGG ( expression, separator ) [ <order_clause> ] <order_clause> ::= WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Implementation
So, Lets we start with an implementation of our requirement to concatenate value from multiple rows to a single string in the SQL server and for that, first, we will create a sample temporary table variable and insert few of the dummy records for the demonstration then we will write an SQL statement for concatenating value from multiple rows to a single string.
Create a Temp table variable
DECLARE @Employee AS TABLE ( Department NVARCHAR(50) , EmployeeName NVARCHAR(50) )
Insert Records into Created Table
INSERT INTO @Employee ( Department, EmployeeName) VALUES ( N'ProductDevelopment', N'Nikunj Satasiya'), ( N'ProductDevelopment', N'Hiren Dobariya'), ( N'ProductDevelopment', N'Sapna Patel'), ( N'sales', N'Vivek Ghadiya'), ( N'sales', N'Pritesh Dudhat'), ( N'Network and Security', N'Hardik Goriya'), ( N'Human Resource', N'Sneh Patel')
Now, we will write an SQL Statment for concatenates values of multiple rows into a single string.
List all the department wise employee names using STRING_AGG() aggregate function (supported in SQL Server version 2017 and later).
Note: The STRING_AGG() aggregate function is supported only and only the SQL Server version 2017 and later.
SELECT Department, STRING_AGG(ISNULL(EmployeeName, ' '), ',') As EmployeeName FROM @Employee GROUP BY Department ORDER BY Department
Explanation
As you can see in the SQL statement above here we have used STRING_AGG() aggregate function to concatenate value from multiple rows to a single string. The STRING_AGG() aggregate function will ignore null values and the corresponding separator will not add. To return a place holder for the null values we have to use the ISNULL function as shown in the SQL statement above where we have replaced null value with the blank sting as well as also used string separator as comma(',').as we wats to concatenate value comma separated. You can change the separator as per the requirement or need.
Output
Summary
In this article, we have learned an efficient, reliable, and easy way to concatenate value from multiple rows to a single string in the SQL server as well as basic information about the aggregate function.STRING_AGG() function, it's syntax, and uses in SQL server.
