Insert “dummy” record into each group

  
  
 create table manufacturingprocess ([Job#] int, Process varchar(30), Hours int, ProcessOrder int)

Insert into manufacturingprocess values(1,'Cut',10,50),(1,'Assemble',25,100)
,(2,'Paint',5,25),(2,'Cut',12,25),(2,'Assemble',12,25)

--Query 
merge manufacturingprocess tgt
using (select distinct [Job#] From manufacturingprocess ) src 
on src.[Job#]=tgt.[Job#] and ( 1=0 or /* avoid insert again if execute twice */ tgt.Process='Dummy r')
When Not matched then 
insert ([Job#],Process,Hours, ProcessOrder) values (src.[Job#],'Dummy r',0,1);

Select [Job#],Process,Hours, ProcessOrder from manufacturingprocess
Order by [Job#],Hours

drop table manufacturingprocess

 

https://social.msdn.microsoft.com/Forums/en-US/df3b593f-27c1-4ca1-aa6f-a8df71fe10a5/insert-dummy-record-into-each-group?forum=transactsql

Advertisements

---