SQL Server STRING_AGG() Function is string function which concatenates values for any given string expressions and separate the string by specified separator.
String_Agg() function was introduced in SQL Server 2017.
It does not add the separator at the end of the result string.
String expression values are implicitly converted to string types and then concatenated.
STRING_AGG ( expression, separator ) [ <order_clause> ]
expression is any type that can be converted to VARCHAR and NAVARCHAR during the concatenation.
separator is the separator for the concatenated string. It can be a VARCHAR and NVARCHAR type.
order_clause to sort the order of concatenated string using WITHIN Group clause –
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Return type of STRING_AGG() function is depends on first argument (expression). If input argument is string type VARCHAR and NAVARCHAR then result type will be same as input type.
Lets take an example of STRING_AGG() Function in SQL Server.
To demonstrate an example, first we create a sample table – PhoneNoList then will insert some dummy records in table.
Create table PhoneNoList ( PhonId int , UserId int, PhoneNo varchar(50) )
Now we will insert record into in table
Insert into PhoneNoList values (1, 501,'9808090909'), (2, 501,'011346456'),(3, 501,'6180808087'),(4, 501,'6767678975'), (5, 502,'78987898788'),(6, 502,'011546456'),(7, 503,'2345353256'), (8, 503,'89089089079'),(9, 504,'03453545'),(10, 504,'9180872323'), (11, 504,'001123543'),(12, 504,'9079657565')
As you can see, table consists the row wise multiple phone numbers for users as shown below.
Now we will use STRING_AGG() Function to concatenate the rows of phone no for specific user into one single string with separated by comma.
Select Userid , String_Agg(PhoneNo,',') as PhoneNoLists from PhoneNoList group by UserId
we can also sort the concatenated string by using WITHIN GROUP clause in STRING_AGG() Function.
To sort the string in ascending order, By default order by clause sort the result in ascending order.
so we do not need to pass keyword ASC for sorting the phone no in ascending order.
Select Userid , String_Agg(PhoneNo,‘, ‘) WITHIN GROUP (ORDER BY PhoneNo) as PhoneNoLists
group by UserId
To sort the string in descending order , we will use keyword DESC in Order by Clause
Select Userid , String_Agg(PhoneNo,', ') WITHIN GROUP (ORDER BY PhoneNo DESC) as PhoneNoLists from PhoneNoList group by UserId
If you are not using SQL Server 2017 or later version, then you can use below T-SQL to get the same output.
SELECT userId, STUFF(( SELECT N', ' + PhoneNo FROM PhoneNoList WHERE UserId = x.UserId ORDER BY UserId FOR XML PATH(''), TYPE).value(N'.', N'nvarchar(max)'), 1, 2, N'')as PhoneNoList FROM PhoneNoList AS x GROUP BY USerId