iconcat(x;d)

Read(12) Label: parameter, concatenate, string,

Description:

Concatenate distinct values of the aggregation field into a string, separated by a separator.

Syntax:

iconcat(x;d)

Note:

Concatenate the distinct values of aggregation field x, using separator d to separate them. If d is omitted, concatenate the values directly without a separator. Can only be used as an aggregation function within groups.

Parameter:

x

Aggregation field name.

d

The separator.

Option:

@c

Use comma as the separator.

@i

Wrap distinct values with single quotation marks for concatenation.

@q

Wrap distinct values with quotation marks for concatenation. Do not handle them this way by default.

@0

Discard nulls and empty strings.

Return value:

String

Example:

 

A

 

1

=demo.query("select top 10 EID,NAME,STATE,DEPT from employee")

Return a table sequence:

2

=A1.groups(DEPT;iconcat(STATE;"_"):ALL_STATE)

Group A1’s sequence by DEPT field, concatenate the STATE values within each group using _ as the separator, and return the result in ALL_STATE column:

3

=A1.groups(DEPT;iconcat@c(STATE):ALL_STATE)

With @c option, use commas as the separator:

4

=A1.groups(DEPT;iconcat@q(STATE):ALL_STATE)

With @q option, wrap each distinct value with the quotation marks for concatenation:

5

=A1.groups(DEPT;iconcat@i(STATE):ALL_STATE)

With @i option, wrap each distinct value with the single quotation marks for concatenation:

6

=A1.groups(DEPT;iconcat@i(STATE;"|"):ALL_STATE)

With @i option, wrap each distinct value with the single quotation marks for concatenation and use | as the separator:

 

When @0 option is used:

 

A

 

1

=create(id,num).record([1,11,1,,1,33,2,232,2,577,2,null])

Return a table sequence:

2

=A1.groups(id;iconcat(num;";"))

Group A1’s sequence by id and aggregate the data, concatenating the distinct values of the aggregation field with semicolons:

3

=A1.groups(id;iconcat@0(num;";"))

With @0 option, discard empty strings and nulls: