Pivot Table - Columns to Rows -

Question

I am looking for a way to create a report like this:

Map mon to = start_date
Map tue to = start_date +1
Map wed to = start_date +2

Table:
+---------+------------+-----+-----+-----+------+-----+-----+-----+------------+-----------+
| Empl_ID | Project ID | mon | tue | wed | thur | fri | sat | sun | start_date | end_date  |
+---------+------------+-----+-----+-----+------+-----+-----+-----+------------+-----------+
|      95 | Admin      |   2 |   2 |   2 |    2 |   2 |   0 |   0 | 4/24/2017  | 4/30/2017 |
|      95 | BH         |   3 |   3 |   3 |    3 |   3 |   0 |   0 | 4/24/2017  | 4/30/2017 |
|      95 | Fiber      |   2 |   2 |   2 |    2 |   2 |   0 |   0 | 4/24/2017  | 4/30/2017 |
|      95 | E-Rate     |   1 |   1 |   1 |    1 |   1 |   0 |   0 | 4/24/2017  | 4/30/2017 |
|     111 | E-Rate     |   1 |   1 |   1 |    1 |   1 |   1 |   0 | 4/24/2017  | 4/30/2017 |
|     111 | Admin      |   2 |   2 |   2 |    2 |   2 |   2 |   0 | 4/24/2017  | 4/30/2017 |
+---------+------------+-----+-----+-----+------+-----+-----+-----+------------+-----------+

Desired Output:
+---------+-----+--------+----+
| 4/24/17 |  95 |  Admin |  2 |
| 4/24/17 |  95 |  BH    |  3 |
| 4/25/17 |  95 |  Admin |  2 |
+---------+-----+--------+----+

Answer 1

IMHO you can easily UNPIVOT it using a CROSS JOIN solution.

First I've added a new table to the solution with the 7 days of week:

create table if not exists dw(d int);
insert into dw values (0),(1),(2),(3),(4),(5),(6);

Then cross join this table to obtain the Cartesian product result set:

select     date_add(Start_Date, interval dw.d day) as "Date",
           Empl_ID,
           Project_ID,
           case dw.d when 0 then mon 
                     when 1 then tue
                     when 2 then wed
                     when 3 then thur
                     when 4 then fri
                     when 5 then sat
                     when 6 then sun
           end as "Val"
from       foo
cross join dw;

Now for every start_date you'll get 7 records.

This is the (reduced) result:

| Date                | Empl_ID | Project_ID | Val |
|---------------------|---------|------------|-----|
| 24.04.2014 00:00:00 | 95      | Admin      | 2   |
| 24.04.2014 00:00:00 | 95      | BH         | 3   |
| 24.04.2014 00:00:00 | 95      | Fiber      | 2   |
| 24.04.2014 00:00:00 | 95      | E-Rate     | 1   |
| 24.04.2014 00:00:00 | 111     | E-Rate     | 1   |
| 24.04.2014 00:00:00 | 111     | Admin      | 2   |
| 25.04.2014 00:00:00 | 95      | Admin      | 2   |
| 25.04.2014 00:00:00 | 95      | BH         | 3   |
| 25.04.2014 00:00:00 | 95      | Fiber      | 2   |
| 25.04.2014 00:00:00 | 95      | E-Rate     | 1   |
| 25.04.2014 00:00:00 | 111     | E-Rate     | 1   |
........
........
| 29.04.2014 00:00:00 | 111     | Admin      | 2   |
| 30.04.2014 00:00:00 | 95      | Admin      | 0   |
| 30.04.2014 00:00:00 | 95      | BH         | 0   |
| 30.04.2014 00:00:00 | 95      | Fiber      | 0   |
| 30.04.2014 00:00:00 | 95      | E-Rate     | 0   |
| 30.04.2014 00:00:00 | 111     | E-Rate     | 0   |
| 30.04.2014 00:00:00 | 111     | Admin      | 0   |

Rextester here