I am trying to pivot this table
timetableId, assignmentId, dateChecked, hoursWorked
1, 11, 2017-09-10, 5
2, 12, 2017-09-10, 5
3, 13, 2017-09-11, 8
4, 11, 2017-09-11, 8
5, 12, 2017-09-11, 8
6, 13, 2017-09-10, 8
so that it would show
assignmentId, tenth, eleventh
11, 5, 8
12, 5, 8
13, 0, 8
I have the following code based on the tutorial from this website http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/
create view timetable_extended as (
select
timetables.assignmentId,
case when dateChecked = '10-09-2017' then timetables.hoursWorked end as tenth,
case when dateChecked = '11-09-2017' then timetables.hoursWorked end as eleventh
from timetables
);
create view timetable_extended_Pivot as (
select
assignmentId,
sum(tenth) as tenth,
sum(eleventh) as eleventh
from timetable_extended
group by assignmentId
);
create view timetable_extended_Pivot_Pretty as (
select
assignmentId,
coalesce(tenth, 0) as tenth,
coalesce(eleventh, 0) as eleventh
from timetable_extended_Pivot
);
however for some reason the first view returns all values as null instead of doing what it is supposed to do - namely this
assignmentId, tenth, eleventh
11, 5, NULL
11, NULL 8
12, 5, NULL
12, NULL 8
13, NULL, 8
What am I doing wrong? Do I need to convert the date to a string?
I have tried the same code with string as the main column and it works perfectly
create table User_Items
(
Cust_Names varchar(10),
Item_Type varchar(50),
Item_Amount float
);
insert into User_Items values
('Alison', 'Computer', 345.39),
('Alison', 'Monitor', 123.45),
('Jason', 'Computer', 435.34),
('Jason', 'Monitor', 158.23),
('Jason', 'Software', 243.54);
create view User_Items_Extended as (
select
User_Items.Cust_Names,
case when Item_Type = "Computer" then Item_Amount end as Computer,
case when Item_Type = "Monitor" then Item_Amount end as Monitor,
case when Item_Type = "Software" then Item_Amount end as Software
from User_Items
);
create view User_Items_Extended_Pivot as (
select
Cust_Names,
sum(Computer) as Computer,
sum(Monitor) as Monitor,
sum(Software) as Software
from User_Items_Extended
group by Cust_Names
);
create view User_Items_Extended_Pivot_Pretty as (
select
Cust_Names,
coalesce(Computer, 0) as Computer,
coalesce(Monitor, 0) as Monitor,
coalesce(Software, 0) as Software
from User_Items_Extended_Pivot
);
