How can I turn a bunch of rows into aggregated columns WITHOUT using pivot in SQL Server 2005?
Posted
by cdeszaq
on Stack Overflow
See other posts from Stack Overflow
or by cdeszaq
Published on 2010-04-27T16:46:39Z
Indexed on
2010/04/27
16:53 UTC
Read the original article
Hit count: 320
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?
© Stack Overflow or respective owner
