declare @Count table (id int,Status varchar(2))
insert into @Count
values(1,'C'),(2,'C'),(3,'P'),(4,'C'),(5,'P'),(6,'P'),(7,'C')
select * from
(select count(Status)'Pending' from @Count where Status='P')T,
(select count(Status)'Completed' from @Count where Status='C')V