CODEWITHSUNDEEP
mysqlsqlmatrixmariadb
Simon Trichereau
Simon Trichereau

Reputation: 809

MySQL : How to do a Matrix combining three tables

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.

Upvotes: 0

Views: 48

Answers (1)

Ali Fidanli
Ali Fidanli

Reputation: 1372

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

Upvotes: 1

Related Questions