mysql pivot help using date vaule -

i starting off in sql , have run issue. can create temp table fine data need shows hours worked user on given date range... dates vary , ...

user hours worked_date bill  8     01-04-17 bill  6     02-04-17 bob   7     01-04-17 

however need this...

user  01-04-17  02-04-17 bill    8         6 bob     7       

i can in ms access no trouble cant work in mysql.. , guides leave me lost. appreciated.

thanks lea

unfortunately there's nothing similar ms access's transform ... pivot statement in mysql, can leverage dynamic sql achieve goal.

set @sql = null;  select group_concat(distinct          concat('sum(case when worked_date = ''', worked_date,                 ''' hours end) `', worked_date, '`'))   @sql   table1;  set @sql = concat('select user, ', @sql, '                       table1                      group user');  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

output:

 | user | 2017-01-04 | 2017-02-04 | |------|------------|------------| | bill |          8 |          6 | |  bob |          7 |     (null) | 

here sqlfiddle

---

you can wrap in stored procedure simplify things in client code

delimiter $$ create procedure user_worked_hours() begin     set @sql = null;      select group_concat(distinct              concat('sum(case when worked_date = ''', worked_date,                     ''' hours end) `', worked_date, '`'))       @sql       table1;      set @sql = concat('select user, ', @sql, '                           table1                          group user');      prepare stmt @sql;     execute stmt;     deallocate prepare stmt; end$$ delimiter ; 

and use it:

call user_worked_hours(); 

here sqlfiddle