[转]行列转秩的存储过程

转自：翔炎

这是我第一次发技术性的文章，如果写得不好，或者有什么纰漏，欢迎大家指正，在此先表示感谢。

我们在现实工作中，经常出现2维的表格，比如这个学生成绩表：

	语文	数学	英语
张三	71	72	73
李四	61	62	63
王五	51	52	53

如果业务要求不仅可以增加行，还可增加列，也就是说，这个成绩表要求可以增加或减少某些科目，那么，我们在设计数据库时，往往要设计成这样：

create table kscj (
    name    char (10) not null ,
    subject char (10) not null ,
    score    int null ,
    constraint PK_kscj primary key (name,subject)
)

在数据库中，我们这样存储数据：

name	subject	score
张三	语文	71
张三	数学	72
张三	英语	73
李四	语文	61
李四	数学	62
李四	英语	63
王五	语文	51
王五	数学	52
王五	英语	53

这个表格可以直接存成文本文件倒入数据库，我就偷懒不写插入数据的语句了。

好了，这样我们就把3个学生的成绩放进了数据库，可是问题也就来了：我们怎么把它还原成原来的2维表。
如果name这列或者subject 这列为有限集合，那还好办一点儿，可是如果我既不知道有哪些学生也不知道有什么科目，那怎么办？
OK，现在说说行列转秩，所谓的行列转秩，就是把本来是一列上的数据，变成一行，不过这一行数据有点特殊地位，他们是转秩后2维表的横表头。
好了，到主角登场了，下面是我原来的一个同事写的存储过程，我感觉有复用性，就把它贴了出来。
我不知道是不是有更巧妙的语句或者更好的方法，希望大家不吝赐教，给出这类问题更好的解决方法。

create procedure sp_rowcloumntranspose(@t1 varchar(30),@t2 varchar(30),@t3 varchar(30),@t4 varchar(30)) as 
begin
       set nocount on    
       declare @sql varchar(7999),
                   @field varchar(300)
       select @sql='select '+''',sum(case '+rtrim(@t2)+' when '+''''''''+'+'+rtrim(@t2)+'+'+''''''' then ' +rtrim(@t3) +' end)
                                           ['''+'+'+'rtrim('+rtrim(@t2)+')+'+''']'
       +''' 
       from (select distinct '+rtrim(@t2) +' from ' + rtrim(@t4) + ') as a '
       create table #field(field varchar(300))
       insert into #field exec(@sql)
       set @sql=''
       -----------------------------------------------------
       declare cur_field cursor local for select * from #field
       open cur_field
       fetch cur_field into @field
       while @@fetch_status=0 
              begin
                     select @sql=@sql+@field
                     fetch cur_field into @field
              end
       drop table #field
       close cur_field
       deallocate cur_field
       -----------------------------------------------------
       select @sql =  'select ' + rtrim(@t1) +@sql+' from '++rtrim(@t4)++' group by '+@t1
       print @sql
       exec(@sql)
       set nocount off
end
go

 这个存储过程的代码就到这里，下面我给解释一下，
首先是参数说明，@t1是我们要还原的交叉表的列分类字段,而@t2就是交叉表的行分类字段，@t3是要汇总的字段，也就是交叉表的数据，@t4呢，就是我们要操作的数据表的表名了。
使用方法呢，exec Sp_RowCloumnTranspose 'name','subject','score','kscj'
我们就得到了这样一张表

name	语文	数学	英语
张三	71	72	73
李四	61	62	63
王五	51	52	53

怎么样？效果还不错吧，其实这个存储过程还可以进行汇总统计，比如想同时出每个人和每个科目的平均分，只要稍加修改这个存储过程就OK了，至于其它的好玩的效果，大家自己去探索吧。

希望大家看得愉快：）