表:'test' 有如下列:mys1,mys2,mys3,..........mys30
想求行和:即 mys1+mys2+mys3+..........+mys30
但是我觉的这样太麻烦了。
于是:用下面的方法求的了 mys1+mys2+mys3+..........+mys30
但是 在执行 SELECT @str FROM Test
却是 ‘mys1+mys2+mys3+..........+mys30’ 不知道改怎么修改!
请各位大侠帮帮忙!
DECLARE j_tjysje cursor FOR
SELECT name FROM syscolumns
WHERE id IN (
SELECT id FROM sysobjects WHERE name='test'
) AND name LIKE 'mys%'
DECLARE @colname varchar(100)
DECLARE @str varchar(1000)
set @str=''
OPEN j_tjysje
FETCH next FROM j_tjysje into @colname
WHILE (@@fetch_status)=0
BEGIN
SELECT @str=@str+'+' +@colname
FETCH next FROM j_tjysje into @colname
END
SELECT @str=stuff(@str,1,1,'')
SELECT @str FROM Test
CLOSE j_tjysje
DEALLOCATE j_tjysje
想求行和:即 mys1+mys2+mys3+..........+mys30
但是我觉的这样太麻烦了。
于是:用下面的方法求的了 mys1+mys2+mys3+..........+mys30
但是 在执行 SELECT @str FROM Test
却是 ‘mys1+mys2+mys3+..........+mys30’ 不知道改怎么修改!
请各位大侠帮帮忙!
DECLARE j_tjysje cursor FOR
SELECT name FROM syscolumns
WHERE id IN (
SELECT id FROM sysobjects WHERE name='test'
) AND name LIKE 'mys%'
DECLARE @colname varchar(100)
DECLARE @str varchar(1000)
set @str=''
OPEN j_tjysje
FETCH next FROM j_tjysje into @colname
WHILE (@@fetch_status)=0
BEGIN
SELECT @str=@str+'+' +@colname
FETCH next FROM j_tjysje into @colname
END
SELECT @str=stuff(@str,1,1,'')
SELECT @str FROM Test
CLOSE j_tjysje
DEALLOCATE j_tjysje
DECLARE j_tjysje cursor FOR
SELECT name FROM syscolumns
WHERE id IN (
SELECT id FROM sysobjects WHERE name='test'
) AND name LIKE 'mys%'
DECLARE @colname varchar(100)
DECLARE @str varchar(1000)
set @str=''
OPEN j_tjysje
FETCH next FROM j_tjysje into @colname
WHILE (@@fetch_status)=0
BEGIN
SELECT @str=@str+'+' +@colname
FETCH next FROM j_tjysje into @colname
END
SELECT @str=stuff(@str,1,1,'')
select @sql = 'select '+ @str + ' from TEST '
exec(@sql) CLOSE j_tjysje
DEALLOCATE j_tjysje
这样
declare @str varchar(8000)
SELECT @str=isnull(@str+'+','')+name FROM syscolumns
WHERE id IN ( SELECT id FROM sysobjects where name='test' )
AND name LIKE 'mys%'
select @str
exec('select '+@str+' from test')
insert into tb select 'a',1,2,3,4
insert into tb select 'b',5,6,7,8declare @sum varchar(50)
set @sum='a+b+c+d'
exec('select name,'+@sum+' as [sum] from tb')name sum
a 10
b 26
declare @cols varchar(1024)
declare @sql nvarchar(2048)
set @cols = ''select @cols = @cols + '+' + name from syscolumns
where id = object_id('test')
and name like 'mys%'set @cols = substring(@cols,2,len(@cols) - 1)
set @sql = 'select ' + @cols + ' from test'
select @sql
exec sp_executesql @sql试试看
as
select o.name as table_name,c.name as field_name,t.name as type,c.length as
length,c.isnullable as isnullable
from syscolumns c
join systypes t on c.xtype = t.xusertype
join sysobjects o on o.id=c.id
where o.xtype='U' declare @str nvarchar(1000)
set @str = 'sum(0+'
select top 1 @str = @str +field_name+'+' from fielddesc where field_name like 'mys%'set @str = left(@str, len(@str)-1)set @str = @str + ')'exec('select '+ @str +' from test')