22,209
社区成员
发帖
与我相关
我的任务
分享
declare @s varchar(8000), @tabe char(10)
set @s = 'SELECT A.ID,SUM(B.PRICE+A.COL1) TOTAL into ' + @table +
'FROM (SELECT ID,SUM(COL1) COL1 FROM TB GROUP BY ID) A ' +
'LEFT JOIN TB1 B ON A.ID=B.AID AND YEAR(B.DATE) LIKE 2009 ' +
'GROUP BY A.ID '
exec(@s)
exec usp1 @table
--数据表
create table tb(a int, b char(10), c decimal(18,3), d char(10))
insert tb
select 1, 'a', 2, 'b' union all
select 3, 'c', 4, 'd'
go
--存储过程
create procedure usp1 @table varchar(10) as
declare @s varchar(8000)
set @s = ''
select @s = @s + ',' + case when type in ('bigint', 'decimal', 'float', 'int', 'numeric', 'real', 'smallint', 'tinyint') then 'sum(' + name + ') as ' + name else 'null as ' + name end from
(
select top 100 percent a.name, b.name as type from syscolumns a join systypes b on a.xtype = b.xtype
where a.id = object_id(@table) order by a.colid
) t
set @s = 'select ' + substring(@s, 2, len(@s) - 1) + ' from ' + @table
exec(@s)
go
--执行
exec usp1 'tb'
drop table tb
drop procedure usp1
/*
a b c d
----------- ----------- ---------------------------------------- -----------
4 NULL 6.000 NULL
*/
declare @str varchar(8000)
set @str = ''
select @str = @str+','+a.name+'=case when '''+c.name+''' = ''int'' then sum(isnull('+a.name+',0)) else ''''end' from syscolumns a
inner join sysobjects b on a.id = b.id
inner join systypes c on a.xusertype=c.xusertype
where b.name = 'tb' and c.name = 'int'
set @str = right(@str,len(@str)-1)
set @str = 'select '+@str+' from tb'
print (@str)
exec (@str)
select * from tb
ID Num PID
----------- ----------- -----------
45 4500 21
ID PID Num text
----------- ----------- ----------- ----------
1 NULL 100 222
2 1 200 333
3 2 300 444
4 3 400 555
5 1 500 666
6 NULL 600 777
7 NULL 700 888
8 7 800 999
9 7 900 111
--->测试数据
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(1),[col2] int,[col3] varchar(1),[col4] numeric(2,1))
insert [tb]
select 'a',1,'b',3 union all
select 'c',5,'d',3.5
--->查询
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')+'sum(['+a.name+']) as ['+a.name+']'
from
syscolumns a,systypes b
where
a.xtype=b.xtype
and
a.id=object_id('tb')
and
b.name in('tinyint','smallint','decimal','int','real','money','float','bigint','numeric','smallmoney')
order by colid
set @sql='select '+@sql+' from tb'
exec(@sql)
/**
col2 col4
----------- ----------------------------------------
6 6.5
**/