Sql三种行转列

create table tem
(
id int identity(1,1) primary key,
name nvarchar(30),
result nvarchar(6)
)

insert into tem(name,result) values('jim','胜')
insert into tem(name,result) values('jim','胜')
insert into tem(name,result) values('jim','负')
insert into tem(name,result) values('tom','胜')
insert into tem(name,result) values('tom','负')
insert into tem(name,result) values('sam','负')
insert into tem(name,result) values('sam','负')

id          name                           result
----------- ------------------------------ ------
1           jim                            胜
2           jim                            胜
3           jim                            负
4           tom                            胜
5           tom                            负
6           sam                            负
7           sam                            负

(7 行受影响)

 

1：

select name,count(case when result='胜' then result end) as win,count(case when result='负' t  hen result end) as lose from tem
group by name

2：

select t1.name,
(select COUNT(1) from tem as t2 where t2.name=t1.name and t2.result='胜') as win,
(select COUNT(1) from tem as t3 where t3.name=t1.name and t3.result='负') as lose
from
(select name from tem group by name) as t1

 

3：（Linq）           

System.Data.Linq.DataContext context = new System.Data.Linq.DataContext(connectionString);
            List<Entity> list= context.ExecuteQuery<Entity>("select * from tem").ToList();

            var query = from t in list
                        group t by t.Name into m
                        select new
                        {
                            Name=m.Key,
                            Win=m.Count(n=>n.Result=="胜"),
                            Lose=m.Count(n=>n.Result=="负")
                        };