select s.partNo, p.descript,

sum(case when battState = '99' then qty else '0'end) as CLNT,

sum(case when battState = '1' then qty-newQty else '0' end) as AVAIL,

sum(case when battState = '20' then qty-newQty else '0' end) as QAH,

sum(case when battState = '30' then qty-newQty else '0' end) as BOOST,

sum(case when battState = '31' then qty-newQty else '0' end) as RESTING

from batt_State s, pnLU as p

where 0=0 and s.PartNo = p.PartNo

group by s.partNo, p.descript