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