Definition
The 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. The function that takes all expressions from rows and concatenates them into a single string.
Syntax
STRING_AGG( <expression>[, <delimiter> ] [ <order_by_clause> ] )
where
input_string specifies a VARCHAR or NVARCHAR expression with values to be concatenated. If the input values are a different data type than VARCHAR or NVARCHAR, then implicit casting is attempted
For example, if the NUM column has five integer values (1, 2, 3, 4, 5), then STRING_AGG("NUM",0) returns 102030405.
delimiter Specifies the character to use as a delimiter when aggregating values
order_by_clause Specifies the sort order of the input rows
Example(s)
What to use in Custom SQL field:
STRING_AGG(CASE WHEN [COMPANY_CONTACT: Type] = 'Company Contact' THEN [COMPANY_CONTACT: Name] END)
As an example:
A regular field and a table with existing records:
Using the STRING_AGG query to concatenate the fields into one row, the field output will create a single record with the concatenated records:
Using the tag '<br />' to format or break the record in the report, the query would be constructed as:
STRING_AGG((CASE WHEN [COMPANY_CONTACT: Type] = 'Company Contact' THEN [COMPANY_CONTACT: Name] END), '<br />')
Field Output:
Things to Note
If the input expression is type VARCHAR, the separator cannot be type NVARCHAR. Null values are ignored and the corresponding separator is not added.
This will only work on sites hosted in Azure