select 
a.shiftid,a.[count],
sum(case when b.system like 'cimpack%' then job else 0 end) as cimpack,
sum(case when b.system like 'floortrack%' then job else 0 end) as floortrack
from t1 inner joint t2 on t1.shiftid=b.shiftid
group by a.shiftid,a.[count]