STRING_AGG
Description
The STRING_AGG operator returns the concatenation of strings from the specified source column for each element in a group. The delimiter will always be inserted between the concatenation of the strings. Multiple order by expressions can be used in order to determine the order of the concatenation.
Warning
Operator Performance We recommend to use this aggregation function only together with grouper columns that lead to small groups. Using it without any grouper columns or grouper columns that lead to large groups can have a negative impact on query performance due to the large result strings that will be created.
The STRING_AGG function can only be applied to STRINGs.
Syntax
STRING_AGG ( source_table.column, delimiter [, ORDER BY source_table.column [ASC|DESC] ] )
source_table.column: The column which should be aggregated.
delimiter: The delimiter STRING that is used to separate the strings in the result.
ORDER BY (optional): Elements of the specified column are used to determine the order in which the strings are concatenated in the result. After the column, ASC (for ascending direction) or DESC (descending direction) can be specified. If the order direction is not specified, the ascending (ASC) order is used.
NULL handling
NULL values in the source table column are treated as if the row does not exist.
Examples
[1] The simplest case of STRING_AGG. | ||||||||||||||||||
| ||||||||||||||||||
|
[2] This example shows how STRING_AGG can be used to aggregate only distinct strings. CASE WHEN is used in combination with INDEX_ORDER to select only the first occurrence of a value in the column we want to aggregate. We can then use STRING_AGG to obtain the final result. | |||||||||||
| |||||||||||
|
[3] This example aggregates the Number column by descending order of the Priority column for each CompanyID. | |||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||
|