MySQL Query, Pivoting Table

Question

I'm having trouble building up my Query that will pivot my rows to columns. My Pivot is my DATE. What the User need is to have a view of a full week data.

My Data is constructed like this :

ID - AssemblyDate
----------------------------------
12345 - 2015-01-01
12346 - 2015-01-01
12347 - 2015-01-01
12348 - 2015-01-02
12349 - 2015-01-02
12350 - 2015-01-02
12351 - 2015-01-03
12352 - 2015-01-03
12353 - 2015-01-03

And the Result I'm expecting would be something like this

DAY1 - DAY2 - DAY3
12345  12348  12351
12346  12349  12352
12347  12350  12352

What I have Tried :

SELECT CASE WHEN (AssemblyDate = '2015-01-01') THEN ID ELSE NULL END AS DAY1
       , CASE WHEN (AssemblyDate = '2015-01-02') THEN ID ELSE NULL END AS DAY2
       , CASE WHEN (AssemblyDate = '2015-01-03') THEN ID ELSE NULL END AS DAY3
FROM MyTable
GROUP BY AssemblyDate

This Gives me something like this :

DAY1 - DAY2 - DAY3
12345  NULL   NULL
12346  NULL   NULL
12347  NULL   NULL
NULL   12348  NULL
NULL   12349  NULL
NULL   12350  NULL
NULL   NULL   12351
NULL   NULL   12352
NULL   NULL   12352

but I don't want the nulls ... is there a way I could like GROUP them without the nulls ?

possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Paul Rowe, Apr 15 '15 at 14:25
Not a duplicate. This one is trickier as it doesn't leave anything to group by — pala_, Apr 15 '15 at 14:31

pala_ · Answer 1 · 2015-04-16T00:22:15.187

You need to do a bit of fiddling, as you need to further group those results. Since there's nothing to group them on, we need to engineer something to use for grouping - in this case, one variable per column that we increment whenever that column is the one that matches the assembly date:

select if(assemblydate = '2015-01-01', @d1c := @d1c +1, 
          if(assemblydate = '2015-01-02', @d2c := @d2c + 1, @d3c := @d3c +1)) id,  
       case when assemblydate = '2015-01-01' then id end d1,   
       case when assemblydate = '2015-01-02' then id end d2,   
       case when assemblydate = '2015-01-03' then id end d3  
  from d, (select @d1c := 0, @d2c := 0, @d3c := 0) q;

That query just adds an id column, that increments for each day, the results look like this.

+------+-------+-------+-------+
| id   | d1    | d2    | d3    |
+------+-------+-------+-------+
|    1 | 12345 |  NULL |  NULL |
|    2 | 12346 |  NULL |  NULL |
|    3 | 12347 |  NULL |  NULL |
|    1 |  NULL | 12348 |  NULL |
|    2 |  NULL | 12349 |  NULL |
|    3 |  NULL | 12350 |  NULL |
|    1 |  NULL |  NULL | 12351 |
|    2 |  NULL |  NULL | 12352 |
|    3 |  NULL |  NULL | 12352 |
+------+-------+-------+-------+

Once that's done, we just wrap another query around the result, group by the id and pick up the values that aren't null with max()

select max(d1), max(d2), max(d3) from
(
  select if(assemblydate = '2015-01-01', @d1c := @d1c +1, 
          if(assemblydate = '2015-01-02', @d2c := @d2c + 1, @d3c := @d3c +1)) id,  
       case when assemblydate = '2015-01-01' then id end d1,   
       case when assemblydate = '2015-01-02' then id end d2,   
       case when assemblydate = '2015-01-03' then id end d3  
  from d, (select @d1c := 0, @d2c := 0, @d3c := 0) q
) qq
group by id;

fiddle here

Really awesome, thanks a lot @pala_, I just learned something new today :) — Sam, Apr 15 '15 at 15:37

MySQL Query, Pivoting Table

1 Answers1