Home » SQL Server STRING_AGG Function

SQL Server STRING_AGG Function

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.

SYNTAX

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 –

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
from PhoneNoList
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'.[1]', N'nvarchar(max)'), 1, 2, N'')as PhoneNoList
FROM PhoneNoList AS x
GROUP BY USerId

 

RECOMMENDED POSTS..

SQL Server STRING_SPLIT() Function

SQL Server CONCAT_WS() Function



 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.