I've got a set of data that looks something like this (VERY simplified):
productId Qty dateOrdered
--------- --- -----------
1 2 10/10/2008
1 1 11/10/2008
1 2 10/10/2009
2 3 10/12/2009
1 1 10/15/2009
2 2 11/15/2009
Out of this, we're trying to create a query to get something like:
productId Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
--------- ---- --- --- --- --- --- --- --- --- --- --- --- ---
1 2008 0 0 0 0 0 0 0 0 0 2 1 0
1 2009 0 0 0 0 0 0 0 0 0 3 0 0
2 2009 0 0 0 0 0 0 0 0 0 3 2 0
The way I'm doing this now, I'm doing 12 selects, one for each month, and putting those in temp tables. I then do a giant join. Everything works, but this guy is dog slow.
See Question&Answers more detail:os