Aggregating strings in QGIS

Question

My inputs are in this way :

col1   col2 col3
1      a     c
1      b     c
2      c     c
2      d     b
2      e     z

And I would like to obtain :

col1    col2
1       a, b
2       c, d, e

I tried doing this using the virtual layer and in the query builder using array_agg function but QGIS doesn't seem to recognize this feature.

Ideally I would like to obtain this type of results however :

col1    col2
1       a:c, b:c
2       c:c, d:b, e:z

Answer 1

Please try a Virtual Layer syntax below:

SELECT x.col1 AS col1, Group_Concat(x.colx, ', ') AS col2
FROM (
  SELECT col1, col2 || ':' || col3 AS colx
  FROM your_layer 
) x
GROUP BY x.col1

In the above example your_layer is the part you need to modify.

Answer 2

Using QGIS expressions and the Aggregate algorithm:

expression used in point 5 (see screen):

array_to_string (array_agg (col2 || ':' || col3, group_by: = col1))