1。我们先创建一个数据库表:
CREATE TABLE [dbo].[StudentsScore](
[Student] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Score] [int] NULL
) ON [PRIMARY]
GO
--接下来运行数据记录脚本:
Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '中文', 80 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '数学', 78 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '英语', 92 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '中文', 89 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '数学', 87 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '英语', 75 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '中文', 92 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '数学', 74 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '英语', 65 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '中文', 79 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '数学', 83 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '英语', 81 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '中文', 73 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '数学', 84 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '英语', 93 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '中文', 79 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '数学', 86 );
Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '英语', 84 );
2。
创建第一个存储过程 proc_GetCountColumnsStr,这个主要是返回要汇总列的字符串,如:数学,英语,中文...
create proc [dbo].[proc_GetCountColumnsStr]
@tbName varchar(500),
@tbField varchar(500),
@outStr varchar(500) out
as
declare @temp varchar(200)
set @temp=''
declare @str varchar(200)
set @str=''
exec('declare cur cursor for (select distinct '+@tbField+' from '+@tbName+')')
open cur
fetch next from cur into @temp
while (@@fetch_status=0)
begin
set @str=@str+@temp+','
fetch next from cur into @temp
end
set @str=left(@str,len(@str)-1)
close cur
deallocate cur
set @outStr = @str
--print @test
/*
exec proc_GetCountColumnsStr 'StudentsScore','Subject',''
简单的存储过程返回字符串例子
drop proc proc_test
@outStr varchar(100) out
as
set @outStr='abc'
print @outStr
exec proc_test 'a'
结果是abc
*/
创建第二个存储过程proc_GetPivotTable
create proc [dbo].[proc_GetPivotTable]
(
@tb varchar(max),--表名
@tbFirstField varchar(max),--第一列的字段
@tbField varchar(max),--要汇总的字段
@tbCount varchar(max)--统计字段
)
as
begin
declare @sql varchar(max)
set @sql=''
/*指定输出字段*/
declare @SpecField varchar(max)
set @SpecField=''
exec proc_GetCountColumnsStr @tb,@tbField, @SpecField out
set @sql='select ['+@tbFirstField+'],'+@SpecField+' from
(select ['+@tbFirstField+'],['+@tbField+'],['+@tbCount+'] from '+@tb+') as t'
+' pivot(sum('+@tbCount+') for ['+@tbField+'] in ('+@SpecField+'))'+'as thepivot'
exec(@sql)
end
/*
select * from dbo.StudentsScore
exec proc_GetPivotTable 'StudentsScore','Student','Subject','Score'
*/
3。接下来执行
exec proc_GetPivotTable 'StudentsScore','Student','Subject','Score'
结果集如下:
到此我们的汇总表已经统计出来了,这里不单单只是对学生表进行统计,只要是传入对应的数据库表名,要统计的相关字段参数,就可以得到相对应的统计数据。