Merging multiple point layers with common fields to create single table in QGIS

Question

I have seven point shapefiles at the same geographic location with three common fields ("ID", "name2", "name3") and a fourth field, ("uni_value") that is unique to each shapefile.

All seven shapefiles have been produced by a QGIS Graphical Modeler workflow and would like to combine all layers into a single table for export into a PostgreSQL database.

Example tables below:

Point Layer 1
    ID | name2 | name3 | unique_value1 
Point Layer 2
    ID | name2 | name3 | unique_value2 
Point Layer 3
    ID | name2 | name3 | unique_value3 
.
.
.

Point Layer 7
    ID | name2 | name3 | uni_value7

Final Merged table:

ID | name2 | name3 | uni_value1 | uni_value2 | ... | uni_value7 

Answer 1

There is a possibility using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer....

Let's assume there are two point layers one called 'points1' and second called 'points2', see image below.

With the following query, it is possible to combine all layers into a single table.

SELECT
    "ID",
    "name2",
    "name3",
    "unique1" AS "unique1",
    NULL AS "unique2",
    geometry
FROM
    "points1"
UNION ALL
SELECT
    "ID",
    "name2",
    "name3",
    NULL AS "unique1",
    "unique2" AS "unique2",
    geometry
FROM
    "points2"

The output point layer with its attribute table will look like:

Answer 2

1. Menu Processing > Toolbox > Merge Vector Layers.

2. Field calculator: create a new string (text) field with this expression: concatenate( to_string(unique_value1), concatenator:=',', group_by:=name2 || name3).

3. Menu Processing > Toolbox > Delete duplicate Geometries.