STRING_AGG (Transact-SQL)

Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.

Syntax:

    STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::= WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

Arguments

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.

separator

Is an expression of NVARCHAR or VARCHAR type that is used as separator for concatenated strings. It can be literal or variable.

<order_clause>

Optionally specify order of concatenated results using WITHIN GROUP clause:

WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

<order_by_expression_list>

A list of non-constant expressions that can be used for sorting results. Only one order_by_expression is allowed per query. The default sort order is ascending.

Return Types

Return type depends on first argument (expression). If input argument is string type (NVARCHAR, VARCHAR), result type will be same as input type. The following table lists automatic conversions:

Input expression type	Result
NVARCHAR(MAX)	NVARCHAR(MAX)
VARCHAR(MAX)	VARCHAR(MAX)
NVARCHAR(1...4000)	NVARCHAR(4000)
VARCHAR(1...8000)	VARCHAR(8000)
int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime, datetime2,	NVARCHAR(4000)

Example:

Sample data looks like bellow.

Expected Output like bellow.

Solution: