How can I get individual results in SQL Server CE

Question

I'm using SQL Server CE as my database with asp.net.

I'm querying the database as follows.

SELECT DispatcherName, ActivityType, COUNT(ActivityType) AS Total
FROM Activity
WHERE (BeginTime >= '4/5/2011 12:00:00 AM') AND (EndTime <= '7/13/2011 12:00:00 AM')
GROUP BY DispatcherName, ActivityType
ORDER BY Total DESC

I'm getting the result as follows:

N/A         VOX             311
Olson       State Change     37
Fred        State Change     26
Dave        PTT              25
George      PTT              25
Olson       PTT              23
Dave        State Change     22
Jason       PTT              22
George      State Change     20
Larry       State Change     20
Martin      PTT              20

but I'm expecting the result as follows

N/A      VOX   State Change    PTT        
Olson    300       37          23
Fred       0       26           0
Dave       0       22          25
George    22       20          25

the fields PP, State Change and Vox are activity types.

Could anyone help me please how to do like above?

I'm using SQL Server Compact Edition. Version:3.5

Answer 1

I know this is a years old, but since there is no answer to it yet, here is an article that could help answer how: http://pratchev.blogspot.com/2007/04/pivoting-data-in-sql-server.html

The official way would be using pivot but since in CE its not available, using alternatives for pivot as describe in the article would be the best way to achieve it.

Hope this helps for anyone looking into similar issues.