This work for you?

DECLARE @Temp TABLE (ServerName VARCHAR(10), ServerFunction VARCHAR(3), BackupReqCode TINYINT)

INSERT INTO @Temp VALUES

('Server1','IS',0),

('Server2','Ops',0),

('Server3','Ops',2),

('Server4','Ops',4),

('Server5','IS',1),

('Server6','IS',5),

('Server7','Ops',0),

('Server8','IS',2),

('Server9','Ops',3),

('Server10','Ops',3)

SELECT

BackupReqCode,

ISNULL(SUM(CASE WHEN ServerFunction = 'IS' THEN 1 END),0) AS 'IS',

ISNULL(SUM(CASE WHEN ServerFunction = 'OPS' THEN 1 END),0) AS 'OPS'

FROM

@Temp

GROUP BY

BackupReqCode