sql server纵表变横表

例子一
1.创建视图存储复杂的select语句
create view Student_Score
as
select stu.sName,sub.sTitle,score.scoreValue
from ScoreInfo score
inner join SubjectInfo sub on sub.sId=score.subId
inner join StudentInfo stu on stu.sId=score.stuId
2.纵表变横表
select sName 姓名,
–如果当前的STitle的值是"语文",则输出ScoreValue
max(case sTitle when ‘语文’ then scoreValue end) 语文,
min(case when sTitle=‘数学’ then scoreValue end) 数学,
sum(case sTitle when ‘英语’ then scoreValue end) 英语
from Student_Score
group by sName–不加这个一个人会出现多次
例子二
–班级 男生 女生
–青龙 1 2
1.创建视图存储复杂语句
create view Student_Class_Gender
as
select cTitle,sGender,COUNT(*) as count1
from StudentInfo stu
inner join ClassInfo class on stu.cid=class.cId
group by cTitle,sGender
–cTitle,sGender,count1
–青龙 男 2
…
2.纵表变横表
select cTitle,
max(case sGender when 1 then count1 else 0 end) 男,
max(case when sGender=0 then count1 else 0 end) 女
from Student_Class_Gender
group by cTitle