交叉表

交叉表 交叉表 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