MySQL Cost by Category throughout time

Question

I have a table that looks like this:

Site | Category | Cost | Month |
A    | Hardware | 10   | 1     |
A    | Software | 30   | 1     |
B    | Software | 15   | 1     |
C    | Labor    | 5    | 2     |
...

I need to output this:

Site | Category |  1   |  2  | ...
A    | Hardware | 10   |  0  |
A    | Software | 30   |  0  |
B    | Software | 15   |  0  |
C    | Labor    |  0   |  5  |

What would be the best way to use the records under the Month attribute as column headers and allocate the Cost records under their corresponding Month columns?

Answer 1

SELECT Site, Category, 
   IF(Month=1,Cost,0) as M1,
   IF(Month=2,Cost,0) as M2,
   IF(Month=3,Cost,0) as M3,
   IF(Month=4,Cost,0) as M4,
   IF(Month=5,Cost,0) as M5,
   IF(Month=6,Cost,0) as M6,
   IF(Month=7,Cost,0) as M7,
   IF(Month=8,Cost,0) as M8,
   IF(Month=9,Cost,0) as M9,
   IF(Month=10,Cost,0) as M10,
   IF(Month=11,Cost,0) as M11,
   IF(Month=12,Cost,0) as M12
FROM tablename

Will give a line by line. You can add a GROUP BY if you want one line per Category

SELECT Site, Category, 
   SUM(IF(Month=1,Cost,0)) as M1,
   SUM(IF(Month=2,Cost,0)) as M2,
   SUM(IF(Month=3,Cost,0)) as M3,
   SUM(IF(Month=4,Cost,0)) as M4,
   SUM(IF(Month=5,Cost,0)) as M5,
   SUM(IF(Month=6,Cost,0)) as M6,
   SUM(IF(Month=7,Cost,0)) as M7,
   SUM(IF(Month=8,Cost,0)) as M8,
   SUM(IF(Month=9,Cost,0)) as M9,
   SUM(IF(Month=10,Cost,0)) as M10,
   SUM(IF(Month=11,Cost,0)) as M11,
   SUM(IF(Month=12,Cost,0)) as M12
FROM tablename
GROUP BY Site, Category

Will keep Site, but sum up Category.

Also, if you don't like the Mx name for the field, you can try as '1' .. '2' etc.I think it should work