create table mytable
(
     category  char(1)   not null,
     code char(1)   not null
)

go

insert mytable values ('A','0')
insert mytable values ('B','1')
insert mytable values ('C','2')
insert mytable values ('A','8')
insert mytable values ('B','X')
insert mytable values ('A','8')
insert mytable values ('C','X')

go

select
     a.Category,
     count(*),
     sum(case a.code when '0' then 1 else 0 end),
     sum(case a.code when 'x' then 1 when '8' then 1 else 0 end),
     sum(case a.code when '1' then 1 else 0 end)
from
     mytable a
where
     a.code <> '2'
group by
     a.category

go
drop table mytable
go