In the Aug2018 release of MonetDB, we added a new aggregation function "sys.group_concat", which aggregates an input column into a single string as output. We provide two versions of this aggregate: sys.group_concat(string) and sys.group_concat(string, string). In both versions, the first parameter corresponds to the input string column to be concatenated. In the former the default delimiter is the ',' character, in the latter the second parameter indicates the separator to be used. If either a group in has a NULL value, or the delimiter is NULL, the output will be NULL.
sql>create table demo (a int, b clob); operation successful sql>insert into demo values (1, 'chair'), (1, 'desk'), (2, 'room'), (1, 'decoration'), (2, 'window'), (2, 'sofa'); 6 affected rows sql>select '[' || sys.group_concat(a) || ']' from demo; +---------------+ | L3 | +===============+ | [1,1,2,1,2,2] | +---------------+ 1 tuple sql>select a, sys.group_concat(b) from demo group by a; +------+-----------------------+ | a | L4 | +======+=======================+ | 1 | chair,desk,decoration | | 2 | room,window,sofa | +------+-----------------------+ 2 tuples sql>select a, sys.group_concat(b, '|') from demo group by a; +------+-----------------------+ | a | L5 | +======+=======================+ | 1 | chair|desk|decoration | | 2 | room|window|sofa | +------+-----------------------+ 2 tuples sql>insert into demo values (3, 'car'), (3, NULL); 2 affected rows sql>select '[' || sys.group_concat(b, '-') || ']' from demo group by a; +-------------------------+ | L6 | +=========================+ | [chair-desk-decoration] | | [room-window-sofa] | | null | +-------------------------+ 3 tuples
