--示例--示例数据
create table 表(日期 int,S1 int,S2 int,S3 int,S4 int,S5 int)
insert 表 select 1, 5, 6, 5, 6, 2
union all select 2, 3, 4, 11,1, 1
union all select 3, 6, 9, 0, 6, 4
go--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''S项='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[''+cast([日期] as varchar)+'']=''+cast(['+name+'] as varchar)'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'日期'select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,4,'')
,@s4=stuff(@s4,1,1,'')
,@s5=stuff(@s5,1,15,'')exec('declare '+@s1+'
select '+@s2+'
select '+@s3+'
from 表
select '+@s4+'
exec('+@s5+')')
go--删除测试
drop table 表/*--测试结果S项 1 2 3
---- ----------- ----------- -----------
S1 5 3 6
S2 6 4 9
S3 5 11 0
S4 6 1 6
S5 2 1 4(所影响的行数为 5 行)
--*/
create table 表(日期 int,S1 int,S2 int,S3 int,S4 int,S5 int)
insert 表 select 1, 5, 6, 5, 6, 2
union all select 2, 3, 4, 11,1, 1
union all select 3, 6, 9, 0, 6, 4
go--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''S项='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[''+cast([日期] as varchar)+'']=''+cast(['+name+'] as varchar)'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'日期'select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,4,'')
,@s4=stuff(@s4,1,1,'')
,@s5=stuff(@s5,1,15,'')exec('declare '+@s1+'
select '+@s2+'
select '+@s3+'
from 表
select '+@s4+'
exec('+@s5+')')
go--删除测试
drop table 表/*--测试结果S项 1 2 3
---- ----------- ----------- -----------
S1 5 3 6
S2 6 4 9
S3 5 11 0
S4 6 1 6
S5 2 1 4(所影响的行数为 5 行)
--*/
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''S项='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[S''+cast([日期] as varchar)+'']=''+cast(['+name+'] as varchar)'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'日期'select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,4,'')
,@s4=stuff(@s4,1,1,'')
,@s5=stuff(@s5,1,15,'')exec('declare '+@s1+'
select '+@s2+'
select '+@s3+'
from 表
select '+@s4+'
exec('+@s5+')')