MySQL pivot to make a row into a colum

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

relevant: https://stackoverflow.com/questions/7674786/mysql-pivot-table — Kalecser, Nov 24 '17 at 01:28

score 1 · Accepted Answer · answered Nov 24 '17 at 04:14

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

awesome! just what i needed. Thanks – user2666633 Nov 24 '17 at 08:10 — user2666633, Nov 24 '17 at 08:10

score 0 · Answer 2 · answered Nov 24 '17 at 01:10

I think that you cannot have this response structure directly from one simple select

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

because your database is getting one row foreach retailers/customer order.

I know that using a server language like PHP or Java you will can handle the data and retrive like you want.

MySQL pivot to make a row into a colum

2 Answers2