Flatten Postgers left join query result with dynamic values into one row

Question

I have two tables products and product_attributs. One Product can have one or many attributs and these are filled by a dynamic web form (name and value inputs) added by the user as needed. For example for a drill the user could decide to add two attributs : color=blue and power=100 watts. For another product it could be 3 or more different attribus and for another it could have no special attributs.

products

| id        | name         | identifier  | identifier_type  | active
| ----------|--------------|-------------|------------------|---
| 1         | Drill        | AD44        | barcode          | true
| 2         | Polisher     | AP211C      | barcode          | true
| 3         | Jackhammer   | AJ2133      | barcode          | false
| 4         | Screwdriver  | AS4778      | RFID             | true


product_attributs

|id        | name         | value       | product_id 
|----------|--------------|-------------|---------- 
|1         | color        | blue        | 1          
|2         | power        | 100 watts   | 1          
|3         | size         | 40 cm       | 2          
|4         | energy       | electrical  | 3 
|4         | price        | 35€         | 3

so attributs could be anything which are set dynamically by the user. My need is to generate a report on CSV which contain all products with their attributs. Without a good experience in SQL I generated the following basic request :

SELECT pr.name, pr.identifier_type, pr.identifier, pr.active, att.name, att.value 
FROM products as pr
LEFT JOIN product_attributs att ON pr.id = att.product_id

as you know the result will contain for the same product as many rows as attributs it has and this is not ideal for reporting. The ideal would be this :

|name       | identifier_type | identifier | active | name   | value | name  | value
|-----------|-----------------|------------|--------|--------|-------|------ |------
|Drill      | barcode         | AD44       | true   | color  | blue  | power | 100 w
|Polisher   | barcode         | AP211C     | true   | size   | 40 cm | null  | null
|Jackhammer | barcode         | AJ2133     | true   | energy | elect | price | 35 €
|Screwdriver| barcode         | AS4778     | true   | null   | null  | null  | null

here I only showed a max of two attributes per product but it could be more if needed. Well I did some research and came across the pivot with crosstab function on Postgres but the problem it requests static values but this does not match my need.

thanks lot for your help and sorry for duplicates if any.

score 0 · Answer 1 · answered Nov 13 '21 at 10:37

Thanks Laurenz Albe for your help. array_agg solved my problem. Here is the query if someone may be interested in :

SELECT
    pr.name, pr.description, pr.identifier_type, pr.identifier,
    pr.internal_identifier, pr.active,
    ARRAY_TO_STRING(ARRAY_AGG (oa.name || ' = ' || oa.value),', ') attributs
FROM
    products pr
LEFT JOIN product_attributs oa ON pr.id = oa.product_id

GROUP BY
    pr.name, pr.description, pr.identifier_type, pr.identifier,
    pr.internal_identifier, pr.active
ORDER BY
    pr.name;

Flatten Postgers left join query result with dynamic values into one row

1 Answers1