0

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 |
+---------+-----+--------+----+

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.