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:
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
The return type of STRING_AGG function 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 will create a sample table named PhoneNoList then insert some dummy records in table.
CREATE TABLE dbo.PhoneNoList(
PhoneId INT,
UserId INT,
PhoneNo VARCHAR(50)
)
Lets insert records in table.
ย INSERT INTO PhoneNoList (PhoneId, UserId, PhoneNo) 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 in below output of table, a single user can have multiple phone numbers.
For example, UserId =501 have four phone numbers and userId =2 have two phone numbers, in similar way other users are having multiple phone number.
[Read Also SQL Server STRING_SPLIT() Function ]
Lets assume, you want to display these multiple phone numbers of each user into single column by separating each phone numbers with comma.
You can use STRING_AGG function, following T-SQL query uses STRING_AGG function which concatenate the rows of phone numbers for each user into one single string with separated by comma.
Select UserId , String_Agg(PhoneNo,',') AS PhoneNoLists FROM PhoneNoList GROUP BY UserId
You can also sort the order of concatenated string by using WITHIN GROUP clause in STRING_AGG Function.
Sorting the string in ascending order
By default Order by clause sort the result in ascending order. So its an optional either specifying keyword ASC or not with Keyword Order By.
Following T-SQL query sorts the phone numbers string in ascending order.
Select UserId , String_Agg(PhoneNo,', ')ย WITHIN GROUP(ORDER BY PhoneNo ASC) AS PhoneNoLists FROM PhoneNoList GROUP BY UserId
Sorting the string in descending order
Following T-SQL query sorts the phone numbers string in descending order.
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
Read Also..
SQL Server STRING_SPLIT() Function
SQL Server CONCAT_WS() Function
8,842 total views, 2 views today