--预备建表
create Table #BBB
(
通知号 int,
工序号 nvarchar(10),
生产数 int
)
--填数据
insert into #BBB (通知号,工序号,生产数)values(2,'工序1' ,20)
insert into #BBB (通知号,工序号,生产数)values(2,'工序1' ,20)
insert into #BBB (通知号,工序号,生产数)values(2,'工序2' ,21)
insert into #BBB (通知号,工序号,生产数)values(2,'工序4' ,22)
insert into #BBB (通知号,工序号,生产数)values(3,'工序1' ,54)
insert into #BBB (通知号,工序号,生产数)values(3,'工序3' ,55)
insert into #BBB (通知号,工序号,生产数)values(3,'工序5' ,56)
select * from #BBB
declare @col nvarchar(500)
declare @str nvarchar(1000)
set @col=''
select @col=@col+',isnull((select sum(生产数) from #BBB where 通知号=a.通知号 and 工序号='''+工序号+'''),0) as '+工序号+'' from #BBB group by 工序号
select @str='select 通知号'+ @col+' from #BBB a group by 通知号'
exec(@str)
drop table #BBB
