0

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?

chiborg
  • 26,978
  • 14
  • 97
  • 115
  • 1
    possible duplicate of [mysql query to dynamically convert row data to columns](http://stackoverflow.com/questions/1599788/mysql-query-to-dynamically-convert-row-data-to-columns) – Rowland Shaw Sep 16 '10 at 12:46
  • YES, but it would require scripting to build out your SQL-Select statement, but don't have MySQL readily available to build such simulation of a Cross-Tab resultset – DRapp Sep 16 '10 at 12:48
  • 1
    What you're describing is known as transposing, cross-tabulating, or pivoting. Not sure if you can do this with a single SELECT, at least not in MySQL without another language doing some work for you. Check out this article for some ideas: http://onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html – sasfrog Sep 16 '10 at 12:52

2 Answers2

1

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

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • While I still need to know what values I have in the category_id column, this solution is easy enough to generate the SQL SELECT with a small script. – chiborg Sep 17 '10 at 10:19
0

Here is a possible solution.

http://www.futhark.ch/mysql/106.html

codingguy3000
  • 2,695
  • 15
  • 46
  • 74