ju_feng 2003年08月28日
*********存储过程中行列转换变量怎么使用?*************
create table tmpa(物料 varchar(100),数量 int,价格 money)
insert tmpa values('A', 100 ,0.50)
insert tmpa values('A', 200 ,0.45)
insert tmpa values('A', 800 ,0.30)
insert tmpa values('B', 100 ,0.80)
insert tmpa values('B', 200 ,0.50)


select 0 id,* into tmpb from tmpa



declare @a int,@b varchar(100)
set @a=1
update tmpb set @a=case when @b=物料 then @a+1 else 1 end,@b=物料,id=@a


declare @sql varchar(8000)
set @sql = 'select 物料'
select @sql = @sql + ',sum(case id when '+cast(id as varchar)+' then 数量 else 0 end) as [数量('+cast(id as varchar)+')],sum(case id when '+cast(id as varchar)+' then 价格 else 0 end) as [价格('+cast(id as varchar)+')]'
from (select distinct id from tmpb) as a
select @sql = @sql+' from tmpb group by 物料'

print(@sql)
exec(@sql)
go
drop table tmpa ,tmpb
这是我看到的一个行列转换的例子
现在我需要将表tmpb用变量代替 比如
declare @a int,@b varchar(100)
set @a=1
update tmpb set @a=case when @b=物料 then @a+1 else 1 end,@b=物料,id=@a


declare @sql varchar(8000),@tabname varchar(10)
set @tabname = 'tmpb'
set @sql = 'select 物料'
select @sql = @sql + ',sum(case id when '+cast(id as varchar)+' then 数量 else 0 end) as [数量('+cast(id as varchar)+')],sum(case id when '+cast(id as varchar)+' then 价格 else 0 end) as [价格('+cast(id as varchar)+')]'
from (select distinct id from tmpb) as a
select @sql = @sql+' from tmpb group by 物料'

print(@sql)
exec(@sql)
go
drop table tmpa ,tmpb
那select @sql = @sql + ',sum(case id when '+cast(id as varchar)+' then 数量 else 0 end) as [数量('+cast(id as varchar)+')],sum(case id when '+cast(id as varchar)+' then 价格 else 0 end) as [价格('+cast(id as varchar)+')]'
from (select distinct id from tmpb) as a
这条语句用该怎么实现相应的功能呢?
用类似这种方法exec('select @sql = @sql + ',sum(case id when '+cast(id as varchar)+' then 数量 else 0 end) as [数量('+cast(id as varchar)+')],sum(case id when '+cast(id as varchar)+' then 价格 else 0 end) as [价格('+cast(id as varchar)+')]'
from (select distinct id from '+@tmpb+') as a')
...全文
9 点赞 收藏 6
写回复
6 条回复

还没有回复,快来抢沙发~

发动态
发帖子
MS-SQL Server
创建于2007-09-28

1.4w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区
社区公告
暂无公告