Skip to main content

Aggregate into a single string

In the Aug2018 release of MonetDB, we added a new aggregation function "group_concat", which aggregates an input column into a single string as output. We provide two versions of this aggregate: group_concat(string) and 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 '[' || group_concat(a) || ']' from demo;
+---------------+
| L3            |
+===============+
| [1,1,2,1,2,2] |
+---------------+
1 tuple
sql>select a, group_concat(b) from demo group by a;
+------+-----------------------+
| a    | L4                    |
+======+=======================+
| 1    | chair,desk,decoration |
| 2    | room,window,sofa      |
+------+-----------------------+
2 tuples
sql>select a, 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 '[' || group_concat(b, '-') || ']' from demo group by a;
+-------------------------+
| L6                      |
+=========================+
| [chair-desk-decoration] |
| [room-window-sofa]      |
| null                    |
+-------------------------+
3 tuples