if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[MaterialID] varchar(6),[PacQty] int,[Qty] int,[Detail] varchar(26),[FieldSum] varchar(100))
insert [tb]
select 1,'',1,2,'100016*1,040076*1,100014*1',null union all
select 2,'040092',2,3,'040092*1',null union all
select 3,'110022',4,2,'110022*1',null
go

select ID,MaterialID,PacQty,Qty,Detail,
FieldSum=case when len(isnull(MaterialID,''))=0 then replace(detail,'*1','*'+ltrim(qty))
else MaterialID+'*'+ltrim(PacQty*Qty) end
from tb
/**
ID          MaterialID PacQty      Qty         Detail                     FieldSum
----------- ---------- ----------- ----------- -------------------------- ---------------------------------
1                      1           2           100016*1,040076*1,100014*1 100016*2,040076*2,100014*2
2           040092     2           3           040092*1                   040092*6
3           110022     4           2           110022*1                   110022*8

(3 行受影响)
**/