交叉表（报表）

有时候，需要将数据库中的数据交叉显示在一张表上。 

        姓名 科目 分数

  1. 张三 语文 74
  2. 张三 数学 83
  3. 张三 物理 93
  4. 李四 语文 74
  5. 李四 数学 84
  6. 李四 物理 94
  7. 李四 化学 97

变为：姓名 化学 数学 物理 语文 平均分  总分

        李四  97   84    94    74   87.25  349
        张三  0     83    93    74   83.33  250 

 1 CREATE TABLE Grade
 2 (
 3 stuName varchar(20),
 4 Subject varchar(20),
 5 Result int
 6 )
 7 
 8 insert into Grade(stuName , Subject , Result) values('张三' , '语文' , 74)
 9 insert into Grade(stuName , Subject , Result) values('张三' , '数学' , 83)
10 insert into Grade(stuName , Subject , Result) values('张三' , '物理' , 93)
11 insert into Grade(stuName , Subject , Result) values('李四' , '语文' , 74)
12 insert into Grade(stuName , Subject , Result) values('李四' , '数学' , 84)
13 insert into Grade(stuName , Subject , Result) values('李四' , '物理' , 94)
14 insert into Grade(stuName , Subject , Result) values('李四' , '化学' , 97)
15 go
16 Select * FROM Grade;
17 
18 ------------Subject只有语文、数学、物理这三门课程（静态SQL）-----------
19 
20 SELECT stuName Name,
21     max(case subject when '语文' then result else 0 end) 语文,
22     max(case subject when '数学' then result else 0 end) 数学,
23     max(case subject when '物理' then result else 0 end) 物理
24 FROM Grade
25 group by stuName
26 
27 /*
28 Name         语文        数学        物理          
29 ---------- ----------- ----------- ----------- 
30 李四         74          84          94
31 张三         74          83          93
32 */
33 
34 
35 --------平均分，总分------
36 
37 SELECT stuName Name,
38     max(case subject when '语文' then result else 0 end) 语文,
39     max(case subject when '数学' then result else 0 end) 数学,
40     max(case subject when '物理' then result else 0 end) 物理,
41     cast(avg(result*1.0) as decimal(18,2))平均分,
42     sum(result)总分
43 FROM Grade
44 group by stuName
45 /*
46 姓名         语文        数学        物理         平均分        总分          
47 ---------- ----------- ----------- -------  ------------- ----------- 
48 李四         74          84          94            84.00         252
49 张三         74          83          93            83.33         250
50 */
51 
52 
53 -----subject不止语文、数学、物理这三门课程（动态SQL）----
54 declare @sql varchar(8000)
55 set @sql='select stuName as '+'Name'
56 select @sql=@sql+',max(case Subject when '''+Subject+'''then Result else 0 end)['+Subject+']'
57 from (select distinct Subject from Grade)as a
58 set @sql=@sql+'from Grade group by stuName'
59 exec (@sql)
60 /*
61 姓名         数学        物理        语文          
62 ---------- ----------- ----------- ----------- 
63 李四         84          94          74
64 张三         83          93          74
65 */
66 declare @sql varchar(8000)
67 set @sql='select stuName as '+'Name'
68 select @sql=@sql+',max(case Subject when '''+Subject+'''then Result else 0 end)['+Subject+']'
69 from (select distinct Subject from Grade)as a
70 set @sql=@sql+',cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result)总分 from Grade group by stuName'
71 exec (@sql)
72 /*
73 姓名         语文        数学        物理    化学        平均分        总分          
74 ---------- ----------- ----------- ------- ----------- ------------- ----------- 
75 李四         74          84          94       97          87.25         349
76 张三         74          83          93        0          83.33         250
77 */

posted @ 2011-10-10 11:05  eva.xiao  阅读(315)  评论(0)  编辑  收藏  举报