CODEWITHSUNDEEP
sqlpostgresqljoin
Anthony
Anthony

Reputation: 35928

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

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

Upvotes: 1

Views: 77

Answers (2)

dwir182
dwir182

Reputation: 1549

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

Upvotes: 0

GMB
GMB

Reputation: 222392

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

Upvotes: 3

Related Questions