Q:

Spatial query to return multiple values

I have a woodland scheme advice layer that has a polygon for each area of advice (brown layer in the picture). I also have a polygon layer of UK local authorities (red boundary layer in the picture).
I would like to perform a spatial query which returns the local authority name for each woodland advice scheme. This would work fine if each woodland advice scheme was found in just one local authority boundary.
However many are found across a boundary of two different authorities. For instance, one scheme sits across the boundaries of the authorities of Fife and Angus. The scheme is the yellow selected polygon in the picture and the red line is the authority boundary. How do I get the spatial join to return a value of Fife; Angus (or something similar to identify two values) instead of just Fife or Angus.

A:

You can create a virtual layer and make use of the group_concat function to aggregate the names
Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query. Replace the layer name for yours
select a.*, group_concat(b.name,'; ') as red_names
from brown a
join red b
 on st_intersects(a.geometry, b.geometry)
group by a.ID

