MS SQL Server STRING_AGG() Function

Sometimes you may need to aggregate cell values of multiple rows in MS SQL Server. There is a function called STRING_AGG in MS SQL Server by which you can do this. STRING_AGG function concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

Let’s see the function syntax:

STRING_AGG(expression, separator)
[WITHIN GROUP(ORDER BY <order_by_expression_list>[ASC|DESC])];

In this syntax:

• Parameter expression is an expression of any type. Expressions are converted to NVARCHAR or VARCHAR types during concatenation. Non-string types are converted to NVARCHAR type..
• Parameter separator is an expression of NVARCHAR or VARCHAR type that is used as separator for concatenated strings. It can be literal or variable.
• WITHIN GROUP clause is optional. <order_by_expression_list>  is a list of non-constant expression that can be used for sorting results. Only one order_by_expression is allowed per query. The default sort order is ascending.
• Function return type is depends on first argument (expression). If input argument is VARCHAR type it will return VARCHAR otherwise it will return NVARCHAR type.

STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions. If the input expression is type VARCHAR, the separator cannot be type NVARCHAR. Null values are ignored and the corresponding separator is not added. I have shown here an interesting example how we can use the STRING_AGG function in our real life.

SELECT 
r.id AS retailer_id,
STRING_AGG(CONVERT(VARCHAR(MAX), d.pending_devices), ';') 
AS agent_pending_devices
FROM 
(
SELECT id 
FROM [DataAnalytics].[dbo].[tbla_retailers]
)AS r
INNER JOIN 
(
SELECT id,retailer_id 
FROM [DataAnalytics].[dbo].[tbla_agent]
)AS a
ON r.id = d.agent_id 
INNER JOIN
(
SELECT agent_id,
CAST(agent_id as VARCHAR)+'('+
STRING_AGG(CONVERT(VARCHAR(MAX),device_id), ',')+
')' as pending_devices
FROM [DataAnalytics].[dbo].[tbla_device]
WHERE device_status=0
) AS d
ON a.id = d.agent_id 
GROUP BY r.id
ORDER BY r.id;

Here is the result set.

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to join multiple rows of string with delimiter using STRING_AGG function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like the facebook page http://www.facebook.com/LearningBigDataAnalytics and stay connected.