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
  • 1
    Something like concatenate(concat("col2",':',"col3"), group_by:="col1", concatenator:=', ') ?
    – Kazuhito
    May 15, 2018 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, 2018 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, 2018 at 15:48
  • Of course. I double checked that. Also, made the field long enough. Still, only sporadic results.
    – Lay
    May 16, 2018 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, 2018 at 7:49

2 Answers 2


Please try a Virtual Layer syntax below:

SELECT x.col1 AS col1, Group_Concat(x.colx, ', ') AS col2
  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.

enter image description here


Using QGIS expressions and the Aggregate algorithm:

expression used in point 5 (see screen):

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.