This query gives output as you required please try

SELECT temp.BackupReqCode,max(case WHEN temp.ServerFunction='IS' THEN temp.CountOfOpsServer ELSE 0 END ) as 'CountOfISServer',

max(case WHEN temp.ServerFunction='Ops' THEN temp.Countofserver ELSE 0 END ) as 'CountOfOpsServer' from

(SELECT BackupReqCode,ServerFunction,count(ServerName)as 'Countofserver'

from TabName

group by BackupReqCode,ServerFunction) temp

group by temp.BackupReqCode

Regards,

Arjun Singh Shekhawat