Iznogud Iznogud - 6 months ago 23
SQL Question

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 just need to make MySQL query and PHP cross table (not sure is this the correct term) so it looks something like this:

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


So far I got to the :

SELECT date, GROUP_CONCAT(CONCAT_WS('|', name, hours) ORDER BY name) schedule
FROM days
GROUP BY date


But I thing I got stuck on populating and making the table like the one above.

Answer

Try this query:

SELECT Name
   ,GROUP_CONCAT(CASE WHEN Date = 11 THEN hours ELSE NULL END) AS `11`
   ,GROUP_CONCAT(CASE WHEN Date = 12 THEN hours ELSE NULL END) AS `12`
   ,GROUP_CONCAT(CASE WHEN Date = 13 THEN hours ELSE NULL END) AS `13`
  FROM days
 GROUP BY Name

You can also use this dynamic query for the same:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN `Date` = ''',
      `Date`,
      ''' THEN hours ELSE NULL END) AS `',
      `Date`, '`'
    )
  ) INTO @sql
FROM Days;

SET @sql = CONCAT('SELECT Name, ', @sql,'
                     FROM Days
                    GROUP BY Name
                  ');

PREPARE stmt FROM @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

Comments