NEW T-SQL FEATURES IN SQL SERVER 2017- III [ STRING_AGG() ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2017.

This function concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

Notes:-

1. Now we do not have to use the old XML trick.
2. STRING_AGG is available in any compatibility level.
3. STRING_AGG aggregate takes all expressions from rows and concatenates them into a single string.
4. Null values are ignored.
5. The return type is depends on first argument (expression). If input argument is string type (NVARCHAR, VARCHAR), result type will be same as input type.
6. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions. Please refer below table for automatic conversions

Example

Data Generation

--

CREATE TABLE Agg
(
	 DepartId INT
	,EmpName VARCHAR(100)
)
GO

INSERT INTO Agg VALUES
(1,'Pawan'),
(1,'Ramesh'),
(2,'Krishan'),
(3,'Sharlee'),
(2,'Nepa'),
(2,'Chandni'),
(4,'Bob')
GO


--

Solution using STRING_AGG function

--

SELECT DepartId, 
    STRING_AGG (EmpName, ',') WITHIN GROUP (ORDER BY DepartId ASC) Names 
FROM dbo.Agg 
GROUP BY DepartId; 

--

OUTPUT

--

DepartId    Names
----------- -----------------------------
1           Pawan,Ramesh
2           Krishan,Nepa,Chandni
3           Sharlee
4           Bob

(4 rows affected)

--

Refer below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com