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?
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