22,210
社区成员
发帖
与我相关
我的任务
分享
Create table Stock(Identify nvarchar(20),Zone nvarchar(20),Qty int)
Insert Stock
select N'A001',N'A-0001',380 union all
select N'A001',N'B-0002',260 union all
select N'A001',N'C-0003',400 union all
select N'A002',N'A-0002',80 union all
select N'A002',N'A-0003',10 union all
select N'A003',N'A-0004',80 union all
select N'A003',N'A-0005',10 union all
select N'A003',N'C-0003',80 union all
select N'A003',N'C-0006',100
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([标记] nvarchar(24),[分区] nvarchar(26),[数量] int)
Insert #T
select N'A001',N'A-0001',380 union all
select N'A001',N'B-0002',260 union all
select N'A002',N'A-0002',80
Go
--测试数据结束
DECLARE @sql1 VARCHAR(8000)
SET @sql1='select 标记'
SELECT @sql1=@sql1+' , sum(case 分区 when '''+ 分区 +''' then 数量 else 0 end) ['+ 分区 +']'
from (SELECT DISTINCT 分区 from #T) as a
SET @sql1=@sql1+' , sum(数量) 总分 from #T group by 标记'
exec(@sql1)