How to aggregate multiple rows in a field in a joined aggregated query

Question

I have two tables:

Table 1:

id | color | qty
---|-------|-----
1  | red   | 10
2  | green | 20
3  | blue  | 50

Table 2:

id | shade  
---|-------
1  | light
1  | dark   
2  | light
3  | light
3  | dark

How can I write a query that joins the two tables and returns the shades as a comma delimited string i.e.

1 | red   | light, dark | 10
2 | green | light       | 20
3 | blue  | light, dark | 50

Possible duplicate of [How to concatenate strings of a string field in a PostgreSQL 'group by' query?](https://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query) — philipxy, Dec 31 '18 at 02:53

score 3 · Accepted Answer · answered Dec 29 '18 at 20:05

If you are using version 9.0 or higher, you can use an aggregated query with « string_agg » :

SELECT 
    t1.id,
    t1.color,
    string_agg(t2.shade, ', ')
    t1.quantity
FROM
    table1 as t1
    INNER JOIN table2 t2 on t2.id = t1.id
GROUP BY
    t1.id,
    t1.color,
    t1.quantity

score 0 · Answer 2 · answered Dec 31 '18 at 01:53

In case if your version below 9.0 you can try this.

select 
  t1.id,
  t1.color,
  array_to_string(array_agg(t2.shade), ','),
  t1.qty
from
  tbl1 as t1
  inner join tbl2 as t2 on t2.id = t1.id
group by
  t1.id,
  t1.color,
  t1.qty
order by
  t1.id

You can check here for Demo

How to aggregate multiple rows in a field in a joined aggregated query

2 Answers2