MySQL – How to Join Tables and Pivot Data

MySQLpivot

tbl_orders

order_id, qty
123, 2
124, 1
125, 3
126, 1
127, 1
128, 1
129, 4

tbl_score

order_id, name, created
123, product 1, 2018-06-01
124, product 1, 2018-06-01
125, product 1, 2018-06-02
126, product 3, 2018-06-02
127, product 2, 2018-06-03
128, product 3, 2018-06-03
129, product 3, 2018-06-03

Required output

             2018-06-01       2018-06-02        2018-06-03
product 1        3                  3               0
product 2        0                  0               1
product 3        0                  1               5

The following query produces the required output for 2018-06-01 but takes around 1 min and 45 seconds to complete on the live database. If I add more dates using case it takes more time. Can someone help me fine tune it or perhaps suggest another solution? 

select s.name, 
  sum( case when date(s.created) = '2018-06-01' then o.qty else 0 end ) as 'June 1'  
from tbl_score s  inner join tbl_order o 
 on s.order_id=o.order_id group by s.name;

Best Answer

Pivoting should be broken into two steps.

Step 1: Create a SELECT with the data not yet pivoted:

SELECT product, date, qty
    FROM ...

In your case, that query is likely to have a JOIN. (I am uncomfortable with which table date is in, but that is a separate issue.)

Step 2: Do the Pivoting.

SELECT product,
       ( ... ) AS '2018-06-01',
       ( ... ) AS '2018-06-02',
       ...
    FROM ( the-above-SELECT ) AS x

There could be issues with step 2 -- As shown, it requires a fixed number of columns. If you have a variable number of columns, you cannot build it this way, but need to construct the SELECT dynamically, then 'execute' it.

More discussion: http://mysql.rjweb.org/doc.php/pivot

Related Question