Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

Create MySQL view using distinct values as columns

⇧
2
⇩
77 views

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.

mysql view distinct create values
asked January 30, 2011 3:27 am CST
kelly
posted via StackOverflow

1 Answer

⇧
3
⇩
 

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.

answered January 30, 2011 4:23 am CST
Mark Byers

Your answer

Join with account you already have

Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
PHP / MySQL - Create array of distinct values, query db table for data associated with those values, and loop for each
January 25, 2011
MySQL Count Distinct Values Query Question
January 14, 2011
Php - create columns from mysql list
January 16, 2011
Get Distinct Values with Sorted Data
January 21, 2011
Fetching a table with unique or distinct values?
January 28, 2011
How can I take a UNION of two separate sql queries and get only DISTINCT values.
December 28, 2010
How can I find all the distinct pair of values in a table.
January 24, 2011
Select one distinct column, others can be non distinct. - MySQL
January 6, 2011
Create link to sort columns PHP
January 22, 2011
Ruby on Rails ignores UUID columns during db:create
January 28, 2011
How can I extract the values from a record as individual columns in postgresql
January 15, 2011
PostgreSQL - create a function that is using data from two columns to update another column
January 16, 2011
BestPractise: Create a left join or add a few extra columns to a certain table?
January 24, 2011
MySQL create table containing values from a column in another table.
January 28, 2011
Why distinct produces syntax error when mixed with other none distinct rows?
January 21, 2011
Could not create unique index - claims to have duplicate values but it doesn't
January 18, 2011
Exclude certain columns from SHOW COLUMNS mysql
January 21, 2011
Mysql: How do I fill in null values in one row with values from another row in the same table?
January 8, 2011
How to get the last 3 distinct ids from a mysql table
January 28, 2011
How do I handle NULL values in a mysql SELECT ... OUTFILE statement in conjunction with FIELDS ESCAPED BY? NULL values are currently being truncated
January 3, 2011
Can I change the datatype for the "Create Table" field in the Show Create Table function in MySQL?
January 10, 2011
MySQL: What if I only want to select by distinct for 1 field?
December 26, 2010
Mysql: complex DISTINCT / COUNT
January 4, 2011
MySQL How to Return Unique/Distinct Results?
January 11, 2011
Select Distinct Field on Join in MySQL
January 18, 2011
Mysql view index
January 24, 2011
MySql: Is it reasonable to use 'view' or I would better denormalize my DB?
January 2, 2011
MySQL default value based on view
January 29, 2011
View-related privileges in MySQL
January 17, 2011
Is a MySQL view faster than a normal query?
January 24, 2011