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

Question

I have a table (people) that contains 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 (s) that includes the following:

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

Basically, I want the results to be as follows:

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

So far, I could use MAX(CASE WHEN people.type='owner' THEN people.name END) AS owner_name , but I can’t get the seller name to add to another line ("im not sure if this is possible even with SQL statements or if I have to do some formatting using the results after that, any advice on concatenating strings would be helpful, or a simple “impossible” would let me know what the limitations are.

Thanks in advance!

Answer 1

You can 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

Answer 2

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