LINQ TO SQL 实现行转列

表结构如下：

id          NAME                 result ----------- -------------------- ------ 1           jim                  胜 2           jim                  胜 3           jim                  负 4           Tom                  胜 5           Tom                  负 6           Sam                  负 7           Sam                  负

想得到的结果是：

NAME                 胜           负 -------------------- ----------- ----------- jim                  2           1 Sam                  0           2 Tom                  1           1

SQL可以这样来实现：

SELECT t1.name,   (SELECTCOUNT(1)FROM temp t2 WHERE t2.NAME=t1.NAME AND t2.result='胜')AS'胜',   (SELECTCOUNT(1)FROM temp t3 WHERE t3.NAME=t1.NAME AND t3.result='负')AS'负' FROM (SELECT NAME FROM temp GROUPBY NAME ) AS t1name                胜          负-------------------- ----------- -----------jim                 2           1Sam                 0           2Tom                 1           1(3row(s) affected)

用LINQ怎么来写呢？

var query=from t in Temps    group t by t.NAMEinto m    selectnew    {       NAME=m.Key,       胜=m.Count(n=>n.Result=="胜"),      负=m.Count(n=>n.Result=="负")    };

这是LINQ翻译成的SQL语句:

 

-- Region Parameters DECLARE @p0 NVarChar(1) = N'' DECLARE @p1 NVarChar(1) = N''-- EndRegion  SELECT[t1][NAME], (SELECT COUNT(*)FROM [temp] AS[t2] WHERE ([t2][result] = @p0) AND ((([t1][NAME] IS NULL) AND ([t2][NAME] IS NULL)) OR (([t1][NAME] IS NOT NULL) AND ([t2][NAME] IS NOT NULL) AND ([t1][NAME] = [t2][NAME])))) AS [], (SELECT COUNT(*)FROM [temp] AS[t3]WHERE ([t3][result] = @p1) AND ((([t1][NAME] IS NULL) AND ([t3][NAME] IS NULL)) OR (([t1][NAME] IS NOT NULL) AND ([t3][NAME] IS NOT NULL) AND ([t1][NAME] = [t3][NAME])))) AS[]FROM(SELECT [t0][NAME]FROM [temp] AS[t0]GROUP BY [t0][NAME] ) AS[t1]