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 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 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: A delimiter STRING what should be 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 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

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 only select 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]

The below example aggregates the Number column by the 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: