--建立测试环境
Create Table 表(id varchar(10),出库1 int,出库2 int,入库1 int)
--插入数据
insert into 表
select '1',20,50,100 union
select '2',30,30,60 union
select '4',50,10,100
select * from 表
--测试语句
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' ,'+ [id] + ' ID ' + ', sum(case when id = ' + id + ' then 入库1 - 出库1 -出库2 else 0 end) 库存'
from (select distinct id from 表)a
select @sql = 'select ' + right(@sql,len(@sql) - 2) + ' from 表 '
print @sql
Exec(@sql)
--删除测试环境
Drop Table 表--结果
1 30 2 0 4 40
Create Table 表(id varchar(10),出库1 int,出库2 int,入库1 int)
--插入数据
insert into 表
select '1',20,50,100 union
select '2',30,30,60 union
select '4',50,10,100
select * from 表
--测试语句
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ' ,'+ [id] + ' ID ' + ', sum(case when id = ' + id + ' then 入库1 - 出库1 -出库2 else 0 end) 库存'
from (select distinct id from 表)a
select @sql = 'select ' + right(@sql,len(@sql) - 2) + ' from 表 '
print @sql
Exec(@sql)
--删除测试环境
Drop Table 表--结果
1 30 2 0 4 40
1 As ID1,
(Select SUM(-IsNull(出库1,0)-IsNull(出库2,0)+IsNull(入库1,0)) from TEST Where ID=1) As 库存1,
2 As ID2,
(Select SUM(-IsNull(出库1,0)-IsNull(出库2,0)+IsNull(入库1,0)) from TEST Where ID=2) As 库存2,
4 As ID3,
(Select SUM(-IsNull(出库1,0)-IsNull(出库2,0)+IsNull(入库1,0)) from TEST Where ID=4) As 库存4
from TEST
Create table TEST
(id Int,
出库1 Int,
出库2 Int,
入库1 Int)
--插入数据
Insert TEST Values(1, 20, 50, 100)
Insert TEST Values(2, 30, 30, 60)
Insert TEST Values(4, 50, 10, 100)
GO
--测试
Declare @sql Nvarchar(4000)
Set @sql=N'Select Distinct '
Select @sql=@sql+Rtrim(id)+' As ID'+Rtrim(id)+N',(Select SUM(-IsNull(出库1,0)-IsNull(出库2,0)+IsNull(入库1,0)) from TEST Where ID='+Rtrim(id)+N') As 库存'+Rtrim(id)+','
from TEST
Select @sql=Left(@sql,Len(@sql)-1)+ ' from TEST'
EXEC(@sql)
GO
--删除测试环境
Drop table TEST
GO
--结果
/*
ID1 库存1 ID2 库存2 ID3 库存3
1 30 2 0 4 40
*/