I have 2 tables linked together via 3rd association table:
TABLE NAME: lot
id | description | <other multiple columns> |
1 | descr_string_1 | ... |
2 | descr_string_2 | ... |
TABLE NAME: id_class
id | code | name |
1 | 01 | class_1 |
2 | 02 | class_2 |
3 | 03 | class_3 |
TABLE NAME: association_lot_id_class
lot_id | class_id |
1 | 1 |
1 | 2 |
2 | 3 |
I'm trying to make a new table based on lot
containing concatenated data on related classes:
TABLE NAME: new_table_lot
id | description | <other multiple columns> | class_codes | class_names |
1 | descr_string_1 | ... | "01, 02" | "class_1, class_2" |
2 | descr_string_2 | ... | "03" | "class_3" |
I've tried to use string_agg with different (definitely, wrong) variations based on other SOF answers (e.g. PostgreSQL - JOIN on string_agg) but no luck
SELECT alic.id_class_id, alic.lot_id, ic.code
FROM association_lot_id_class alic
JOIN id_class ic
JOIN (
SELECT id_class_id, string_agg(id.code, ',') AS codes
FROM codes
GROUP BY id_class)