3

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
3

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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