How can I make "month" columns in Sql?
- by Beska
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.
I know this isn't much to go on, but knowing that I barely qualify as a tyro in the db world, I'm wondering if there is a better high level approach to this that I might try. (I'm guessing there is.)
(I'm using MS Sql Server, so answers that are specific to that DB are fine.)
(I'm just starting to look at "PIVOT" as a possible help, but I don't know anything about it yet, so if someone wants to comment about that, that might be helpful as well.)
