PU_STRING_AGG
Description
The PU_STRING_AGG operator returns the concatenation of strings from the specified source column for each element in the given target table. The delimiter will be always inserted between the concatenation of the strings. Multiple order by expressions can be used in order to determine the order of the concatenation.
The PU_STRING_AGG function can only be applied to STRINGs.
Syntax
PU_STRING_AGG ( target_table, source_table.column, delimiter [, filter_expression ] [, ORDER BY source_table.column [ASC|DESC] ] )
target_table: The table to which the aggregation result should be pulled. This can be:
a table from the data model. It needs to be, directly or indirectly, connected to the source_table, and there must be a 1:N relationship between the target_table and the source_table. Further documentation about join relationships can be found in Join functionality.
DOMAIN_TABLE or CONSTANT (see Pull Up Aggregation Table Options).
source_table.column: The column which should be aggregated for every row of the target_table.
delimiter: A delimiter STRING what should be used to separate the strings in the result.
filter_expression (optional): An optional filter expression to specify which values of the source_table.column should be taken into account for the aggregation.
ORDER BY (optional): Elements of the specified column are used to determine the order in which the strings should be 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
If no value in the source table column exists for the element in the target table (either because all values of the source table are filtered out, or because no corresponding value exists in the first place), NULL will be returned. NULL values in the source table column are treated as if the row does not exist.
Examples
[1] Aggregate the activity column per case. | |||||||||||||||||||||||||||
| |||||||||||||||||||||||||||
|
[2] Aggregate the activity column per casewith respect to the ascending sorted order of value 2 and the descending sorted order of value 1. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[3] Aggregate the activity column per case with respect to the ascending sorted order of value while only the activities which have a value between 10 and 30 are concatenated. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[4] Aggregate the activities per case where | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[5] You can use INDEX_ORDER to aggregate only distinct values. This example calculates the distinct currencies involved in each case. | |||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||
|