sql 2005 T_sql 动态行转列（数据汇总）方法

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'

 

结果集如下：

 

 

到此我们的汇总表已经统计出来了，这里不单单只是对学生表进行统计，只要是传入对应的数据库表名，要统计的相关字段参数，就可以得到相对应的统计数据。