Simple cross table with PHP and MySQL query -

i have table:

date name hours -------------- 11    peter   12:00 11    peter   11:00 11    john    10:00 12    peter   9:00 12    john   13:00 13    peter   10:00 13    john    16:00  etc... 

i need make mysql query , php cross table (not sure correct term) looks this:

        11   |  12   | 13  ----------------------------------------- john  | 10:00 | 13:00 | 16:00 ----------------------------------------- peter | 12:00 | 9:00  | 10:00         11:00 |  

so far got :

select date, group_concat(concat_ws('|', name, hours) order name) schedule  days group date 

but thing got stuck on populating , making table 1 above.

try query:

select name    ,group_concat(case when date = 11 hours else null end) `11`    ,group_concat(case when date = 12 hours else null end) `12`    ,group_concat(case when date = 13 hours else null end) `13`   days  group name 

you can use dynamic query same:

set @sql = null; select   group_concat(distinct     concat(       'group_concat(case when `date` = ''',       `date`,       ''' hours else null end) `',       `date`, '`'     )   ) @sql days;  set @sql = concat('select name, ', @sql,'                      days                     group name                   ');  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

output:

|  name |          11 |    12 |    13 | --------------------------------------- |  john |       10:00 | 13:00 | 16:00 | | peter | 12:00,11:00 |  9:00 | 10:00 | 

see this sqlfiddle