sqlserver中查询横表变竖表的sql语句简析

首先是三张表， cno对应的是课程，在这里我就粘贴了。            

                              主表

                 人名表

按照常规查询

select s.sname, c.cname,s2.scgrade

  from s s inner join sc s2 on s2.sno = s.sno inner join c c on c.cno = s2.cno

那么结果是这样的

 

但是这是横表 不是我想看到的结果。

我们要看到这样的结果：

那么怎么办呢？
第一种写法：

select w.sname,
sum(case when w.cno= 1 then w.scgrade else 0 end) as '语文',
sum(case when w.cno =2 then w.scgrade else 0 end) as '数学',
sum(case when w.cno= 3 then w.scgrade else 0 end) as '英语'
from
(select s.sno,s.sname, s2.cno, s2.scgrade from s s inner join sc s2 on s2.sno = s.sno where s.sno in (select c.sno from sc c group by c.sno ))
as w group by w.sname

第二种写法：

select s.sname,
sum(case when s2.cno= 1 then s2.scgrade else 0 end) as '语文',
sum(case when s2.cno =2 then s2.scgrade else 0 end) as '数学',
sum(case when s2.cno= 3 then s2.scgrade else 0 end) as '英语'
from
s s inner join sc s2 on s2.sno = s.sno
inner join c c on c.cno = s2.cno
group by s.sno,
s.sname

这是我工作遇到过得情况，总结下来。如果有遇到这种情况的话可以参考下。