(QGIS 3.4) The image below will help explain my goal:

I'm working out how to coalesce the attributes of the field "ID" if the field "X, Y" is a duplicate.

I have highlighted some duplicate coordinates in red. My expression will create a new text field and chain all attributes in the "ID" field, delimited by a pipe (|).

For example, where I have labelled features with blue text 1,2,3, the new field will write

Special Protection Areas | Scheudled Monuments | SSSI | AONB | Special Areas of Conservtion | Scheduled monuments | Scheduled Monuments | SSSI | AONB

I will omit duplicate values later in excel.

enter image description here

Here's what I have so far...if( "X, Y" = "X, Y" , coalesce( "ID" ),' ') but this just duplicates the "ID" field without any coalescing.

  • 1
    The SQL Coalesce function returns the first NOT NULL field in a list of fields. If you are trying to aggregate IDs with a GROUP BY on the coordinates, you may need to look for a different function.
    – Vince
    Jul 30, 2019 at 10:31
  • 2
    Thanks Vince, I've managed to answer my own question with your guidance.
    – James B
    Jul 30, 2019 at 11:31

2 Answers 2


With credit to Vince, the correct expression to use in this case was:

concatenate( to_string( "ID" ),group_by:="X, Y",  concatenator:='|')

In QGIS I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume there is a point layer with it's corresponding attribute table, see image below.


With the following query, it is possible to achieve the result.

SELECT "X, Y", GROUP_CONCAT(info, ' | ') AS info_concat
FROM "points"

The output Virtual Layer will look like as following


Note: Geometry is not included in the final output, otherwise extend the query with geometry parameter and to check how many points were grouped insert COUNT(), i.e.

SELECT "X, Y", GROUP_CONCAT(info, ' | ') AS info_concat, geometry, COUNT("X, Y")  AS pperloc
FROM "points"


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.