MYSQL: Separate products into columns

Question

This is in relation to my previous post MYSQL: Get quantity per product in each order.

Using the query below, it generates a table of:

SELECT o.order_id, o.username, op.product_id, SUM( op.quantity ) as quantity
FROM  `orders` o JOIN 
      orders_products op
      ON op.order_id = o.order_id
GROUP BY op.product_id, o.order_id
ORDER BY o.order_id, op.product_id;

_______________________________________________
| order_id | username | product_id | quantity |
-----------------------------------------------
|        1 | bill     |          1 |        5 |
-----------------------------------------------
|        1 | bill     |          2 |        3 |
-----------------------------------------------
|        1 | bill     |          3 |        2 |
-----------------------------------------------
|        2 | sally    |          1 |        2 |
-----------------------------------------------
|        3 | jeff     |          1 |        6 |
-----------------------------------------------
|        3 | jeff     |          2 |        7 |
-----------------------------------------------

As seen above, each new product_id is assigned its own row. Is it possible to have additional columns for each product_id instead of rows generating an output same as the one below?

_______________________________________________________________________
| order_id | username | product_qty_1 | product_qty_2 | product_qty_3 |
-----------------------------------------------------------------------
|        1 | bill     |             5 |             3 |             2 |
-----------------------------------------------------------------------
|        2 | sally    |             2 |             0 |             0 |
-----------------------------------------------------------------------
|        3 | jeff     |             6 |             7 |             0 |
-----------------------------------------------------------------------

My tables:

-- Orders
_______________________
| order_id | username |
-----------------------
|        1 | bill     |
-----------------------
|        2 | sally    |
-----------------------
|        3 | jeff     |
-----------------------

-- Products
___________________
|    id | product |
-------------------
|     1 | Table   |
-------------------
|     2 | Chair   |
-------------------
|     3 | Mouse   |
-------------------

-- Order Products
___________________________________________
|   id | order_id | product_id | quantity |
-------------------------------------------
|    1 |        1 |          1 |        5 |
-------------------------------------------
|    2 |        1 |          2 |        3 |
-------------------------------------------
|    3 |        1 |          3 |        2 |
-------------------------------------------
|    4 |        2 |          1 |        2 |
-------------------------------------------
|    5 |        3 |          1 |        6 |
-------------------------------------------
|    6 |        3 |          2 |        7 |
-------------------------------------------

You are looking for `pivot table`. It is already answered how to sole the task in different ways. http://stackoverflow.com/questions/7674786/mysql-pivot-table — Naeel Maqsudov, Aug 10 '15 at 01:28

score 2 · Accepted Answer · answered Aug 10 '15 at 01:15

If you know the products in advance, you can use conditional aggregation:

SELECT o.order_id, o.username,
       SUM(CASE WHEN op.product_id = 1 THEN op.quantity ELSE 0 END) as quantity1,
       SUM(CASE WHEN op.product_id = 2 THEN op.quantity ELSE 0 END) as quantity2,
       SUM(CASE WHEN op.product_id = 3 THEN op.quantity ELSE 0 END) as quantity3
FROM  `orders` o JOIN 
      orders_products op
      ON op.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

If you don't know the product ids, then you would have to use dynamic SQL, or perhaps use GROUP_CONCAT() to create a string with the information you want.

You did it again, Gordon! Superb. – enchance Aug 10 '15 at 02:54 — enchance, Aug 10 '15 at 02:54

MYSQL: Separate products into columns

1 Answers1

Linked