This is how I like to do that kind of thing in SQL 2000. (It's easier in SQL 2005 with the Pivot command.)

if object_id(N'tempdb..#T') is not null

drop table #T;

create table #T (

ID int identity primary key,

TS_USER_11 char(2),

TS_USER_18 char(2));

insert into #T (TS_USER_11, TS_USER_18)

select 'P1','' union all

select 'P1','P2' union all

select 'P2','' union all

select 'P1','' union all

select 'P1','P1' union all

select 'P2','' union all

select 'NP','P2' union all

select 'P1','';

select

(select count(*)

from #T

where TS_USER_11 = 'P1'

and TS_USER_18 = '') as P1,

(select count(*)

from #T

where TS_USER_11 = 'P2'

and TS_USER_18 = '') as P2,

(select count(*)

from #T

where TS_USER_11 = 'NP'

and TS_USER_18 = '') as NP,

(select count(*)

from #T

where TS_USER_18 != '') as Alt;