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

Something like concatenate(concat("col2",':',"col3"), group_by:="col1", concatenator:=', ') ? — Kazuhito, May 15 '18 at 12:48
Yes, something like that. I am trying it in the field calculator, but I only get a result for col1 values for which there is only 1 instance (nothing to group), all the rest is null. — Lay, May 15 '18 at 14:32
Make sure you choose Text(string) as the output field type. If it is set to Whole number (integer) the output is null. — Kazuhito, May 15 '18 at 15:48
Of course. I double checked that. Also, made the field long enough. Still, only sporadic results. — Lay, May 16 '18 at 7:09
OK. It is hard to find the root cause if the results are not definitive. Instead, I will post a Virtual Layer syntax for you to test. — Kazuhito, May 16 '18 at 7:49

Kazuhito · Accepted Answer · 2018-05-16 07:51:38Z

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.

1 Answer 1