Skip to main content

PU_STRING_AGG_DISTINCT

Description

The STRING_AGG DISTINCT operator returns the concatenation of distinct strings from the specified source column for each element in a group. The delimiter is inserted between the concatenated strings. Multiple ORDER BY expressions can be used to specify the concatenation order.

The STRING_AGG DISTINCT function can only be applied to STRING columns.

Syntax

PU_STRING_AGG_DISTINCT ( 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:

  • 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]

Query

Column1 

PU_STRING_AGG_DISTINCT ( "caseTable" , "activityTable"."activity" , ' - ' )

Input

Output

activityTable

caseId : string

activity : string

timestamp : date

value : int

'1'

'A'

Sat Jan 04 2020 22:00:00.000

15

'1'

'B'

Sun Jan 05 2020 23:00:00.000

12

'1'

'B'

Mon Jan 06 2020 00:00:00.000

14

'2'

'C'

Mon Jan 06 2020 12:00:00.000

19

'2'

'D'

Mon Jan 06 2020 14:00:00.000

34

'2'

'D'

Mon Jan 06 2020 14:00:00.000

37

'3'

'Y'

Thu Jan 02 2020 12:00:00.000

10

'3'

'X'

Tue Jan 07 2020 01:00:00.000

9

'3'

'X'

Wed Jan 08 2020 01:00:00.000

18

'3'

'Z'

Thu Jan 09 2020 12:00:00.000

32

caseTable

caseId : string

'1'

'2'

Foreign Keys

activityTable.caseId

caseTable.caseId

Result

Column1 : string

'A - B'

'C - D'

[2]

Query

Column1 

PU_STRING_AGG_DISTINCT ( "caseTable" , "activityTable"."activity" , ' | ' , ORDER BY "activityTable"."value2" ASC , "activityTable"."value1" DESC )

Input

Output

activityTable

caseId : string

activity : string

timestamp : date

value1 : int

value2 : int

'1'

'A'

Sat Jan 04 2020 22:00:00.000

31

15

'1'

'A'

Sat Jan 04 2020 22:00:00.000

33

15

'1'

'B'

Sun Jan 05 2020 23:00:00.000

62

12

'2'

'C'

Mon Jan 06 2020 12:00:00.000

12

19

'2'

'C'

Mon Jan 06 2020 12:00:00.000

17

8

'2'

'D'

Mon Jan 06 2020 14:00:00.000

14

9

'2'

'E'

Mon Jan 06 2020 20:00:00.000

3

9

'2'

'F'

Wed Jan 08 2020 08:00:00.000

15

9

'2'

'G'

Wed Jan 08 2020 10:00:00.000

4

21

'3'

'X'

Tue Jan 07 2020 01:00:00.000

31

9

'3'

'Y'

Thu Jan 02 2020 12:00:00.000

7

10

'3'

'Z'

Thu Jan 09 2020 12:00:00.000

8

32

caseTable

caseId : string

'1'

'2'

'3'

Foreign Keys

activityTable.caseId

caseTable.caseId

Result

Column1 : string

'B | A'

'C | F | D | E | G'

'X | Y | Z'

[3]

Query

Column1 

PU_STRING_AGG_DISTINCT ( "caseTable" , "activityTable"."activity" , ' | ' , "activityTable"."value" BETWEEN 10 AND 30 , ORDER BY "activityTable"."value" )

Input

Output

activityTable

caseId : string

activity : string

timestamp : date

value : int

'1'

'A'

Sat Jan 04 2020 22:00:00.000

15

'1'

'B'

Sun Jan 05 2020 23:00:00.000

12

'1'

'B'

Mon Jan 06 2020 00:00:00.000

14

'2'

'C'

Mon Jan 06 2020 12:00:00.000

19

'2'

'D'

Mon Jan 06 2020 14:00:00.000

34

'2'

'E'

Mon Jan 06 2020 20:00:00.000

9

'2'

'E'

Mon Jan 06 2020 20:00:00.000

14

'2'

'F'

Wed Jan 08 2020 08:00:00.000

17

'2'

'G'

Wed Jan 08 2020 10:00:00.000

21

'3'

'Y'

Thu Jan 02 2020 12:00:00.000

10

'3'

'X'

Tue Jan 07 2020 01:00:00.000

9

'3'

'X'

Wed Jan 08 2020 01:00:00.000

18

'3'

'Z'

Thu Jan 09 2020 12:00:00.000

32

caseTable

caseId : string

'1'

'2'

'3'

Foreign Keys

activityTable.caseId

caseTable.caseId

Result

Column1 : string

'B | A'

'E | F | C | G'

'Y | X'

[4]

Query

Column1 

PU_STRING_AGG_DISTINCT ( "companyDetail" , "activityTable"."activity" , ',' , "companyDetail"."country" <> 'FR' , ORDER BY "activityTable"."eventtime" )

Input

Output

activityTable

caseId : int

activity : string

eventtime : date

1

'A'

Thu Dec 31 2015 22:13:20.000

1

'A'

Thu Dec 31 2015 22:13:20.000

1

'B'

Fri Jan 01 2016 01:00:00.000

1

'C'

Fri Jan 01 2016 03:46:40.000

2

'A'

Thu Dec 31 2015 22:30:00.000

2

'E'

Thu Dec 31 2015 22:46:40.000

2

'F'

Thu Dec 31 2015 22:46:40.000

2

'G'

Thu Dec 31 2015 23:03:20.000

2

'G'

Thu Dec 31 2015 23:03:20.000

3

'A'

Fri Jan 01 2016 03:46:40.000

3

'H'

Fri Jan 01 2016 06:33:20.000

4

'A'

Thu Dec 31 2015 22:15:00.000

5

'A'

Thu Dec 31 2015 22:16:40.000

5

'B'

Thu Dec 31 2015 22:18:20.000

5

'C'

Thu Dec 31 2015 22:20:00.000

6

'A'

Thu Dec 31 2015 22:14:10.000

7

'A'

Fri Jan 01 2016 17:40:00.000

8

'A'

Fri Jan 01 2016 20:26:40.000

companyDetail

caseId : int

country : string

1

'DE'

2

'DE'

3

'UK'

4

'DE'

5

'US'

6

'FR'

7

'DE'

8

'US'

companyResource

caseId : int

resource : int

1

500

2

400

3

100

4

-400

5

50

6

0

7

10

8

5000

Foreign Keys

companyDetail.caseId

activityTable.caseId

companyResource.caseId

activityTable.caseId

Result

Column1 : string

'A,B,C'

'A,E,F,G'

'A,H'

'A'

'A,B,C'

null

'A'

'A'

See also: