sqlmysqlcategories

How can I use column values as column names in MySQL

I have a table that looks like this:

|date      |category_id|val
|2010-08-09|1          |2
|2010-08-09|2          |45
|2010-08-10|3          |1500
|2010-08-10|2          |4

I would like to select from this table, that each category id is a column so the end result looks like this:

|date      |1    |2   |3 
|2010-08-09|2    |45  |NULL
|2010-08-10|NULL |4   |1500

Is something like this possible with a single SELECT statement or stored procedure, without using an extra table and without knowing all category_id values beforehand?

Solution

  • I think you want to use MYSql's sign to do this. Here is a good link that does something very similar to what you are tryign to do:

    http://en.wikibooks.org/wiki/MySQL/Pivot_table