0

I have the three following SQL Tables :

+-------------------------+
|          SHOP           |
+---------+---------------+
| shop_id |  shop_label   |
+---------+---------------+
|       1 | Shop Paris    |
|       2 | Shop Madrid   |
|       3 | Shop New York |
|       4 | Shop Tokyo    |
+---------+---------------+

+----------------------------+
|           PRODUCT          |
+------------+---------------+
| product_id | product_label |
+------------+---------------+
|          1 | Pen           |
|          2 | Workbook      |
|          3 | Smartphone    |
|          4 | Computer      |
|          5 | Chair         |
+------------+---------------+

+-------------------------------------------------------+
|                      COMMAND LINE                     |
+---------+------------+----------+---------------------+
| fk_shop | fk_product | quantity |        date         |
+---------+------------+----------+---------------------+
|       1 |          1 |       10 | 2021-10-20 12:10:59 |
|       4 |          3 |        1 | 2021-10-23 12:11:07 |
|       2 |          2 |        3 | 2021-10-29 12:12:07 |
|       1 |          2 |        8 | 2021-10-30 12:12:37 |
|       1 |          1 |        5 | 2021-11-03 13:10:07 |
+---------+------------+----------+---------------------+

And now I'm trying to make a matrix of all the products as COLUMNS and all the shops as ROWS showing how much product were buyed. I would like to make a query to retrieve this result :

+------------+------------+-------------+---------------+------------+
|            | Shop Paris | Shop Madrid | Shop New York | Shop Tokyo |
+------------+------------+-------------+---------------+------------+
| Pen        |         15 |           8 |               |            |
| Workbook   |            |           3 |               |            |
| Smartphone |            |             |               |          1 |
| Computer   |            |             |               |            |
| Chair      |            |             |               |            |
+------------+------------+-------------+---------------+------------+

Do you know a way to do something like this ? I saw a long time ago a query with the WITH operator to create matrix, but I don't remember it really well...

Thanks for your help.

Simon Trichereau
  • 721
  • 11
  • 19
  • this might solve your problem. https://stackoverflow.com/questions/1241178/mysql-rows-to-columns – ProDec Nov 03 '21 at 11:51

1 Answers1

1

in oracle or sqlserver there is PIVOT() to use , but in MYSQL its not.

So something like this can be done:

select  p.product_label ,
sum(case when s.shop_id = 1 then  cl.quantity else 0 end) Shop_Paris ,
sum(case when s.shop_id = 2 then  cl.quantity else 0 end) Shop_madrid ,
sum(case when s.shop_id = 3 then  cl.quantity else 0 end) Shop_newyork ,
sum(case when s.shop_id = 4 then  cl.quantity else 0 end) Shop_tokyo 
from command_line cl 
inner join product p on p.product_id =  cl.fk_product 
inner join shop s on s.shop_id = cl.fk_shop 
group by p.product_label
Ali Fidanli
  • 1,342
  • 8
  • 12
  • Ok, but to be honest I have like more than 100 shops and more than 100 products, I would like to find a way to don't write every shop in my query ... I would like to find a way to build a matrix automatically ... – Simon Trichereau Nov 03 '21 at 11:48
  • as i said its not possible to use PIVOT in mysql as i know. Lets wait others to see if theres something i dont know ... – Ali Fidanli Nov 03 '21 at 12:13