Coalesce field "X" if field "Y" is a duplicate

Question

(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.

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

Answer 1

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

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

Answer 2

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"
GROUP BY "X, Y"

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"
GROUP BY "X, Y"

---

References:

• SQLite GROUP_CONCAT
• W3Schools | SQL COUNT(), AVG() and SUM() Functions