Create a View in Postgresql with a single row for each parent value and concatenated list of all child values separated by comma

Question

I have two tables, Locations and Layers (Soil Layers) and am trying to figure out how to create a View with one row for each location and one column that lists all of the soil layers in that location separated by a comma in Postgresql. PARENT TABLE (Locations)

loc_id	location
1	TU 1
2	TU 2

CHILD TABLE (Layers)

lyr_id	loc_id	layer
1	1	A
2	1	Ap
3	1	B
4	2	A
5	2	Fill 1
6	2	Fill 2
7	2	B

DESIRED OUTPUT

location	layer
TU 1	A, Ap, Apb
TU 2	A, Fill 1, Fill 2, B

I am a noobie and have accomplished this in QGIS using the relation_aggregate function, but the results are static. In QGIS, the function uses the relationship between the tables and the concatenate function to generate the comma separated list (ex. relation_aggregate(relation:='lyr_aggr',aggregate:='concatenate',expression:="layer", concatenator:=', '). I am hoping to be able to create a dynamic View in postgres that I can use to generate a symbology for testing locations based on the layers that are present, but have not been able to identify an expression with a similar output.

Answer 1

The following query demonstrates the use of STRING_AGG to produce a comma separated list of values aggregated from multiple rows:

WITH
  locations(loc_id, location) AS (
    VALUES (1, 'TU 1'),
           (2, 'TU 2')
  ),
  layers(lyr_id, loc_id, layer) AS (
    VALUES (1, 1, 'A'),
           (2, 1, 'Ap'),
           (3, 1, 'B'),
           (4, 2, 'A'),
           (5, 2, 'Fill 1'),
           (6, 2, 'Fill 2'),
           (7, 2, 'B')
  )
SELECT locations.location,
       STRING_AGG(layers.layer, ', ' ORDER BY layers.lyr_id) AS layer
  FROM locations
    LEFT OUTER JOIN layers
      ON locations.loc_id = layers.loc_id
  GROUP BY locations.location
  ORDER BY locations.location;

The PostgreSQL Documentation is an excellent resource. An approach I recommend to those interesting in expanding their familiarity with PostgreSQL's functions, is to each day practice with a function (or perhaps a group of closely related functions), much as one might use a word-a-day calendar to expand one's vocabulary.