交叉表 交叉表 create procedure prcrosstab @chrrowhead char(30),/*表示列，在交叉表的结果中作为第一列出现*/ @chrcolhead char(30),/*表示列，在交叉表的结果中该列中的数据被变换为新列名称*/ @chrvalue char(30),/*表示列，在该列中执行聚合函数*/ @ch
交叉表 交叉表 create procedure prcrosstab @chrrowhead char(30),/*表示列，在交叉表的结果中作为第一列出现*/ @chrcolhead char(30),/*表示列，在交叉表的结果中该列中的数据被变换为新列名称*/ @chrvalue char(30),/*表示列，在该列中执行聚合函数*/ @chrsource char(30),/*源表或视图*/ @inytype tinyint=1,/*1-求和，2-平均值，3-最小值，4-最大值，5-计数*/ @inygrouping tinyint=0/*1-工作日，2-年内的周数，3-月份，4-季度，5-年份*/ as /*过程变量*/ declare @chvrow varchar(255), @chvcol varchar(255), @chvval varchar(255), @chvtype varchar(10), @chvrowtype varchar(10), @chvcoltype varchar(255), @chvtemp varchar(255), @chvcoltemp varchar(255), @chvrowtemp varchar(255), @inttype int, @introwtype int, @intcoltype int, @chvexec varchar(255), @chvgroup varchar(255), @flttemp float, @dtmtemp datetime, @insr smallint, @intcolumn int, @intreturn int, @inttemp int, @intcolnamelen int, @intmaxrowhead int set nocount on /*检查数据源是否存在*/ if not exists (select * from sysobjects where name=@chrsource and type in('v','u')) begin raiserror 51001 '数据源不存在' return -1 end /*检查列是否存在*/ if not exists (select sc.name from syscolumns sc join sysobjects so on sc.id=so.id where so.name=@chrsource and sc.name=@chrcolhead) begin raiserror 51002 '无效 @chrcolhead 名称' return -1 end if not exists (select sc.name from syscolumns sc join sysobjects so on sc.id=so.id where so.name=@chrsource and sc.name=@chrrowhead) begin raiserror 51002 '无效 @chrrowhead名称' return -1 end if not exists (select sc.name from syscolumns sc join sysobjects so on sc.id=so.id where so.name=@chrsource and sc.name=@chrvalue) begin raiserror 51002 '无效 @chrvalue 名称' return -1 end /*检查聚合函数类型，是否是有效值*/ if @inytype5 begin raiserror 51000 '无效聚合函数类型' return -1 end /*确定聚合函数类型*/ select @chvtype= case @inytype when 1 then 'sum' when 2 then 'avg' when 3 then 'max' when 4 then 'min' when 5 then 'count' else 'sum' end /*取得@chrvalue的数据类型*/ select @chvtemp=t2.name from sysobjects o join syscolumns c on (o.id=c.id) join systypes t1 on (t1.usertype=c.usertype) join systypes t2 on (t1.type=t2.type) where t2.usertype<100 and t2.usertype18 and t2.usertype80 and o.type in ('u','v') and o.name=@chrsource and c.name=@chrvalue /*数据类型分类*/ select @inttemp= case when @chvtemp in ('int','smallint','tinyint','float','real','decimal','numeric','money','smallmoney') then 1 when @chvtemp in ('datetime','smalldatetime') then 3 when @chvtemp in ('bit','char','varchar') then 5 else 100 end /*检查数据类型与聚合类型是否匹配*/ if @inytype<@inttemp begin raiserror 51020 '无效的数据类型 @chrvalue' return -1 end /*转换成合适的数据类型*/ select @chvcoltype=rtrim( case @inytype when 5 then 'int' else case when @chvtemp in ('bit','char','varchar ') then 'int' when @chvtemp in ('decimal','numeric') then 'float' else @chvtemp end end) /*确认数据分组是否有效*/ if @inygrouping5 begin raiserror 51010 '无效的数据分组' return -1 end /*取得@chrcolhead列的合法数据类型*/ select @chvtemp=t2.name from sysobjects o join syscolumns c on (o.id=c.id) join systypes t1 on (t1.usertype=c.usertype) join systypes t2 on (t1.type=t2.type) where t2.usertype0) or (@inttemp=3 and @inygrouping=0) begin raiserror 51030 '分组数据与分组类型不一致' return -1 end /*安全性检查*/ /*此部分以后完成 if user_id()1 begin if (select count(distinct c.name) from syscolumns c,sysobjects o,sysprotects p,sysusers u,master..spt_values v where c.name in (@chrcolhead,@chrrowhead,@chrvalue) */ /*定义临时表*/ create table #colnames(colname varchar(255),colnumber int null) create table #rownames(rowname varchar(255) null) /*创建colnames表*/ select @chvexec='insert #colnames select col1,col2 from' +'(select distinct col1='+ case @inttemp when 3 then case when @inygrouping in (1,3) then 'datename('+ case @inygrouping when 1 then 'weekday' when 3 then 'month' end +','+rtrim(@chrcolhead)+')' else case @inygrouping when 2 then '''week' when 4 then '''quarth' when 5 then '''year' end+'_''+'+'datename('+ case @inygrouping when 2 then 'week' when 4 then 'quarth' when 5 then'year' end+','+rtrim(@chrcolhead)+')' end else case @intcoltype when 1 then 'convert(varchar(255),'+rtrim(@chrcolhead)+')' else rtrim(@chrcolhead) end end+',col2='+ case @inttemp when 3 then 'datepart('+ case @inygrouping when 1 then 'weekday' when 2 then 'week' when 3 then 'month' when 4 then 'quarter' when 5 then 'year' end+','+rtrim(@chrcolhead)+')' else '0' end+',col3='+ case @inttemp when 3 then 'datepart('+ case @inygrouping when 1 then 'weekday' when 2 then 'week' when 3 then 'month' when 4 then 'quarter' when 5 then 'year' end+','+rtrim(@chrcolhead)+')' else rtrim(@chrcolhead) end+' from '+rtrim(@chrsource)+')xyz order by col3' --print @chvexec exec(@chvexec) --select * from #colnames /*检查列计数值*/ if (select count(*) from #colnames)>1023 begin drop table #colnames raiserror 51004 'distinct column count exceeded max of 1023.' return -1 end /*检验名称长度*/ if (select max(datalength(rtrim(colname))-1) from #colnames)>29 begin drop table #colnames raiserror 51050 'column data length exceeded max of 30.' return -1 end /*填写rownames表*/ select @chvexec='insert #rownames select distinct '+ case @introwtype when 1 then 'convert(varchar255),' else '' end+rtrim(@chrrowhead)+ case @introwtype when 1 then ')' else '' end+' from '+@chrsource --print @chvexec exec(@chvexec) /*创建和修改crosstable*/ select @intmaxrowhead= (select max(datalength(rtrim(rowname))) from #rownames) /*创建crosstable*/ /*定义crosstable的rowhead字段*/ create table #crosstable(rowhead varchar(255) null) /*在crosstable中加入列*/ declare colname_cursor2 cursor for select colname from #colnames open colname_cursor2 fetch colname_cursor2 into @chvcol while @@fetch_status>=0 begin select @chvcoltemp='' if @chvcol like '%[^a-z0-9]%' begin select @insr=1 while @insr=0 begin select @chvcoltemp='' if @chvcol like '%[^a-z0-9]%' begin select @insr=1 while @insr<=datalength(rtrim(@chvcol)) begin select @chvcoltemp=rtrim(@chvcoltemp)+ case when substring(@chvcol,@insr,1) like '[a-z0-9_]' then substring(@chvcol,@insr,1) else ' ' end select @insr=@insr+1 end select @chvcol=@chvcoltemp end select @chvexec='update #crosstable set '+ case when substring(@chvcol,1,1) like '[^1234567890]' then @chvcol else '_'+ltrim(@chvcol) end+'='+ case when @chvval is null then '0' else rtrim(@chvval) end+' where rowhead='''+rtrim(@chvrow) select @chvrow= case when @chvrow is null then 'null' else rtrim(@chvrow) end select @chvrowtemp='' if @chvrow like'%' begin select @insr=1 while @insr<=datalength(rtrim(@chvrowtemp))-1 begin select @chvrowtemp=rtrim(@chvrowtemp)+ case when substring(@chvrow,@insr,1) like '[^'']' then substring(@chvrow,@insr,1) else ' '' '' ' end select @insr=@insr+1 end end select @chvrow=@chvrowtemp select @chvexec=@chvexec+@chvrow+'''' --print @chvexec exec(@chvexec) fetch colname_cursor3 into @chvrow,@chvcol,@chvval end close colname_cursor3 deallocate colname_cursor3 commit tran set nocount off select @chvexec='select * from #crosstable' --print @chvexec exec(@chvexec) drop table #colnames drop table #rownames drop table #crosstable