如何在SQL Server CE中获得单个结果 [英] How can I get individual results in SQL Server CE

问题描述

我正在使用SQL Server CE作为asp.net的数据库.

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

我得到的结果如下:

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

但是我期望结果如下

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

字段PP, State Change和Vox是活动类型.

有人可以帮助我如何做上面的事情吗?

Could anyone help me please how to do like above?

我正在使用SQL Server Compact Edition. 版本:3.5

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

推荐答案

我知道这已经有几岁了，但是由于尚无答案，因此这篇文章可以帮助您解答:

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

正式的方法将是使用数据透视，但是由于在CE中尚不可用，因此使用本文所述的数据透视替代方法将是实现此目标的最佳方法.

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.

这篇关于如何在SQL Server CE中获得单个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！