How can I turn a bunch of rows into aggregated columns WITHOUT using pivot in SQL Server 2005?
- by cdeszaq
Here is the scenario:
I have a table that records the user_id, the module_id, and the date/time the module was viewed.
eg.
Table: Log
------------------------------
User_ID Module_ID Date
------------------------------
1 red 2001-01-01
1 green 2001-01-02
1 blue 2001-01-03
2 green 2001-01-04
2 blue 2001-01-05
1 red 2001-01-06
1 blue 2001-01-07
3 blue 2001-01-08
3 green 2001-01-09
3 red 2001-01-10
3 green 2001-01-11
4 white 2001-01-12
I need to get a result set that has the user_id as the 1st column, and then a column for each module. The row data is then the user_id and the count of the number of times that user viewed each module.
eg.
---------------------------------
User_ID red green blue white
---------------------------------
1 2 1 2 0
2 0 1 1 0
3 1 2 1 0
4 0 0 0 1
I was initially thinking that I could do this with PIVOT, but no dice; the database is a converted SQL Server 2000 DB that is running in SQL Server 2005. I'm not able to change the compatibility level, so pivot is out.
How can I accomplish this?
