sql2000创建交叉表的实例分析

例一、教师课程表如下:
教师号　　星期号　是否有课
　1　　　　2　　　有
　1　　　　3　　　有
　2　　　　1　　　有
　3　　　　2　　　有
　1　　　　2　　　有
写一条sql语句让你变为这样的表
教师号　星期一　星期二　星期三
　1　　　　　　　 2　　　 1　
　2　　　1　　　
　3　　　　　　　 1
各星期下的数字表示：对应的教师在星期几已经排的课数

1、创建表：
CREATE TABLE [teacher] (
 [teacherno] [varchar] (50)  NULL ,
 [weekno] [varchar] (50) NULL ,
 [hadcourse] [varchar] (50) NULL
)

2、插入数据：
insert into teacher
select '1','2','有'
union select '1','2','有'
union select '1','3','有'
union select '2','1','有'
union select '3','2','有'
union select '1','2','有'

3、创建交叉表的sql语句：
select teacherno as 教师号
,sum(case when weekno = 1 and hadcourse = '有' then 1 else 0 end) as 星期一
,sum(case when weekno = 2 and hadcourse = '有' then 1 else 0 end) as 星期二
,sum(case when weekno = 3 and hadcourse = '有' then 1 else 0 end) as 星期三
,sum(case when weekno = 4 and hadcourse = '有' then 1 else 0 end) as 星期四
,sum(case when weekno = 5 and hadcourse = '有' then 1 else 0 end) as 星期五
 from teacher group by teacherno

例二、学生分数表PupleMark如下：
1、表数据：
Name Grade Project Mark
甲 1 数学 80
甲 1 语文 70
甲 1 地理 70
甲 1 英语 100
乙 5 语文 75
丙 4 数学 95
乙 5 数学 50
乙 5 英语 87

2、创建表：
CREATE TABLE [PupleMark] (
 [Name] [varchar] (50) NULL ,
 [Grade] [varchar] (50) NULL ,
 [Project] [varchar] (50) NULL ,
 [Mark] [int] NULL
)

3、插入数据：
insert into PupleMark
select '甲','1', '数学',80
union select '甲',1,'语文',70
union select '甲',1,'地理',70
union select '甲',1,'英语',100
union select '乙',5,'语文',75
union select '丙',4,'数学',95
union select '乙',5,'数学',50
union select '乙',5,'英语',87

4、创建交叉表的sql语句：
SELECT Grade as 年级,
Avg(Case Project WHEN  '语文' THEN Mark ELSE NULL END) as 语文,
Avg(Case Project WHEN  '数学' THEN Mark ELSE NULL END) as 数学,
Avg(Case Project WHEN  '英语' THEN Mark ELSE NULL END) as 英语
FROM PupleMark
GROUP BY Grade
ORDER BY Grade

5、结果：
年级  语文  数学  英语
1     70    null  100
3     null   80   null
4     null   95   null
5     75     50   87