Skip to main content

Celonis Product Documentation

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.

Query

Column1

         STRING_AGG ( "Table1"."Column1" , '&&' )
        

Input

Output

Table1

Column1 : string

Column2 : int

'a'

1

null

2

'c'

1

null

2

null

2

Result

Column1 : string

'a&&c'

[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.

Query

Column1

         STRING_AGG ( CASE WHEN INDEX_ORDER ( "Table1"."Column1" , PARTITION BY ( "Table1"."Column1" ) ) = 1 THEN "Table1"."Column1" ELSE NULL END , '-' )
        

Input

Output

Table1

Column1 : string

'one'

'one'

'two'

'three'

Result

Column1 : string

'one-two-three'

[3]

This example aggregates the Number column by descending order of the Priority column for each CompanyID.

Query

Column1

         "Table1"."CompanyID"
        

Column2

         STRING_AGG ( "Table1"."Number" , ', ' , ORDER BY "Table1"."Priority" DESC )
        

Input

Output

Table1

CompanyID : int

Number : string

Priority : int

1

'one'

3

1

'two'

5

2

'one'

2

1

'three'

7

3

'one'

2

2

'two'

4

Result

Column1

Column2

1

three, two, one

2

two, one

3

one

See also: