CODEWITHSUNDEEP
sqlpostgresql
David Ziemann
David Ziemann

Reputation: 970

Combine rows into one result and add results as different values in SQL

I have a table (people) that include the following information:

  id  cert_id  type    name 
  1     123    owner   Paul
  2     123    seller  George
  3     123    buyer   steve
  4     456    owner   micheal

I also have a table (items) that includes the following:

id  cert_id  item_name     
1     123    staples
2     123    cheese
3     123    iguanas
4     456    pie

Basically, what I want to do is have the results as the following:

cert_id  owner_name  seller_name  buyer_name  item_name 
  123    Paul        George       steve       staples, cheese, iquanas
  456    micheal                              pie

So far I have been able to use MAX(CASE WHEN people.type='owner' THEN people.name END) AS owner_name, but I cannot get the seller name to append to the other row ('im not sure if this is even possible via the SQL statement or if I should do some formatting with the results afterwards. Any advice on combining rows would be helpful, or a simple "not possible" would let me know what the limitations are.

Thanks in advance!

Upvotes: 4

Views: 2040

Answers (2)

Waleed Ahmed
Waleed Ahmed

Reputation: 3

This can be accomplished using INNER JOIN or LEFT-OUTER-JOIN...

Upvotes: -1

Taryn
Taryn

Reputation: 247700

You should be able to use the following query to get the result:

select p.cert_id,
  max(case when p.type = 'owner' then p.name end) owner_name,
  max(case when p.type = 'seller' then p.name end) seller_name,
  max(case when p.type = 'buyer' then p.name end) buyer_name,
  array_agg(distinct i.item_name) Items
from people p
inner join items i
  on p.cert_id = i.cert_id
group by p.cert_id;

See SQL Fiddle with Demo

Upvotes: 6

Related Questions