Create MySQL view using distinct values as columns

Question

I have searched a while and can't find an answer to my problem. I have a table that looks like this:

date      name   status  
2011-01-01  m1   online  
2011-01-01  m2   offline  
2011-01-01  m3   online  
2011-01-02  m1   offline  
2011-01-02  m2   offline  
2011-01-02  m3   online  

I want to create a view that will looks like the following:

date         m1     m2       m3  
2011-01-01  online  offline  online  
2011-01-02  offline offline  online  

Where values under the the 'name' column are distinct and has a fixed number of values, say 20 different values (m1 ... m20), but can be increased as times goes. Is it possible to create a view like this? if yes, how?

Many thanks.

Answer 1

Result sets with a variable number of columns are usually a bad idea, however if you really want to do it then you can use a combination of GROUP BY, MAX and IF:

CREATE VIEW yourview AS
SELECT
    date,
    MAX(IF(name = 'm1', status, NULL)) m1,
    MAX(IF(name = 'm2', status, NULL)) m2,
    MAX(IF(name = 'm3', status, NULL)) m3
FROM yourtable
GROUP BY date;

The view will give you this data, as you wanted:

date        m1       m2       m3    
2011-01-01  online   offline  online
2011-01-02  offline  offline  online

Note that it is not possible to create a view with a variable number of columns so you will have to recreate the view every time the number of columns changes.