交叉表 交叉表 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 @inyType<1 or @inyType>5 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.usertype<>18 and t2.usertype<>80 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 @inyGrouping<0 or @inyGrouping>5 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.usertype<100 and t2.usertype<>18 and t2.usertype<>80 and o.type in ('u','v') and o.name=@chrSource and c.name=@chrColHead if upper(@chvTemp) not in ('CHAR','VARCHAR') Select @intColType=1 else Select @intColType=0 /*取得@chrRowHead的合法数据类型*/ Select @chvRowType=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.usertype<>18 and t2.usertype<>80 and o.type in ('u','v') and o.name=@chrSource and c.name=@chrRowHead if upper(@chvRowTemp) not in ('CHAR','VARCHAR') Select @intRowType=1 else Select @intRowType=0 /*检查组分类类型*/ 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 (@intTemp=5 and @inyGrouping>0) or (@intTemp=1 and @inyGrouping>0) 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<=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='alter table #crosstable add '+ Case when substring(@chvCol,1,1) Like '[^1234567890]' then @chvCol else '_'+LTrim(@chvCol) End +' '+@chvColType+' null default (0)' --Print @chvExec Exec(@chvExec) Fetch colname_cursor2 into @chvCol End Close colname_cursor2 Deallocate colname_cursor2 /*加入初始Crosstable数据*/ Select @chvExec='insert #crosstable(rowhead) select rowname from #rownames' --Print @chvExec Exec(@chvExec) /*使用游标填写crosstable的剩余部分*/ /*创建游标*/ Select @chvExec='declare colname_cursor3 cursor for select '+ Case @intRowType when 1 then 'convert(varchar(255),'+RTrim(@chrRowHead)+')' Else RTrim(@chrRowHead) End+','+ Case when @intTemp=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+',total=Convert(varchar(255),'+RTrim(@chvType)+'('+RTrim(@chrvalue)+')) from '+ RTrim(@chrSource)+' group by '+RTRim(@chrRowHead)+','+ 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 'quarter' when 5 then 'year' end+','+RTrim(@chrColHead)+')' end else Rtrim(@chrColHead) End --Print @chvExec Exec(@chvExec) /*更新Crosstable表*/ Begin Tran Open colname_cursor3 Fetch colname_cursor3 into @chvRow,@chvCol,@chvVal while @@fetch_status>=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