Concatenate value from multiple rows to a single string in SQL Server

watch_later Saturday, June 6, 2020
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.

Concatenate value from multiple rows to a single string in SQL Server


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

Concatenate value from multiple rows to a single string

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.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon