• 主页
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

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

from (select distinct id from tmpb) as a

from (select distinct id from '+@tmpb+') as a')
...全文
9 点赞 收藏 6

6 条回复

MS-SQL Server

1.4w+

25.3w+

MS-SQL Server相关内容讨论专区