MySQL pivot将行放入列中

Question

I have three tables, products, customers, order

Product:

id | name |
 1 | milk |
 2 | bread|
 3 | Pea  |

Customer:

id | name  | category
1  | James | retailer
2  | Paul  | vendor
3  | Dave  | retailer

Order:

id  | product_id | customer_id | qty | price
 1  | 1          | 2           | 23  | 50 
 2  | 2          | 2           | 4   | 30
 3  | 3          | 2           | 6   | 10
 4  | 2          | 1           | 9   | 30
 5  | 3          | 1           | 2   | 10
 6  | 1          | 3           | 6   | 50
 7  | 3          | 3           | 7   | 10

When i do a query to show transactions by customers with category of vendor like

SELECT customer.name, product.name as pname, order.qty, order.price FROM customer, product, order 
WHERE customer.id = order.customer_id 
AND product.id = order.product_id AND customer.category = "vendor"

i will get something like:

name |  pname | qty | price
Paul |  milk  | 23  | 50
Paul |  bread | 4   | 30
Paul |  pea   | 6   | 10

I want this instead:

name | milk  | bread  | pea  | total
Paul | 23    | 4      | 6    | 90

While that of retailers will look like this:

SELECT customer.name, product.name as pname, order.qty, order.price FROM 
customer, product, order 
WHERE customer.id = order.customer_id 
AND product.id = order.product_id AND customer.category = "retailer"

I will get a table like this:

 name |  pname | qty | price
James |  bread | 9   | 30
James |  pea   | 2   | 10
 Dave |  milk  | 6   | 50
 Dave |  pea   | 7   | 10

But i want this instead:

name  | milk  | bread  | pea  | total
James | 0     |  9     | 2    | 40
Dave  | 6     |  0     | 7    | 60

Answer 1

Simply use conditional aggregation for pivoting columns. And be sure to use explicit joins instead of the deprecated implicit join as former has been the standard for 25 years in ANSI-92.

SELECT c.name,  
       SUM(CASE WHEN p.name = 'milk' THEN o.qty ELSE 0 END) as milk,
       SUM(CASE WHEN p.name = 'bread' THEN o.qty ELSE 0 END) as bread,
       SUM(CASE WHEN p.name = 'pea' THEN o.qty ELSE 0 END) as pea,
       SUM(o.price) AS Total 
FROM `customer` c
INNER JOIN `order` o
  ON c.id = o.customer_id 
INNER JOIN `product` p
  ON p.id = o.product_id 
WHERE c.category = 'vendor'   -- same for retailer
GROUP BY c.name