Filter row based in same id in T-SQL

Question

I have this table and I need to filter some rows.

If the same number has line with Account = Tax and Account = Pay, I have to exclude these two rows.

For example, the number 1 has one row with tax and one row with pay in account column, so I have to exclude all rows with number 1.

In the same way, number 2 has one row with tax and another with tax too, so I have to keep them.

Can you help me?

number	ACCOUNT	COLOR
1	TAX	BLUE
1	PAY	PINK
2	TAX	RED
2	TAX	GREEN

I expect to get this result:

number	ACCOUNT	COLOR
2	TAX	RED
2	TAX	GREEN

Answer 1

See simple example

select *
from(
  select * 
    ,sum(case when ACCOUNT='TAX' then 1 else 0 end)over(partition by number)tax_qty
    ,sum(case when ACCOUNT='PAY' then 1 else 0 end)over(partition by number)pay_qty
  from test
) t
where tax_qty=0 or pay_qty=0

number	ACCOUNT	COLOR	tax_qty	pay_qty
2	TAX	RED	2	0
2	TAX	GREEN	2	0

Demo

Typo(tax_qty>0 and pay_qty>0) Thank's for @T N.

Answer 2

CREATE TABLE table1 
(
    number  VARCHAR(512),
    ACCOUNT VARCHAR(512),
    COLOR   VARCHAR(512)
);

INSERT INTO table1 (number, ACCOUNT, COLOR) VALUES
    ('1', 'TAX', 'BLUE'),
    ('1', 'PAY', 'PINK'),
    ('2', 'TAX', 'RED'),
    ('2', 'TAX', 'GREEN');

Select * from table1

--numbers that have TAX and PAY
select a.number from table1 a inner join table1 b ON a.number=b.number AND b.ACCOUNT='TAX' where a.Account='PAY'

  
Select * from table1
WHERE number not in (
  select a.number from table1 a inner join table1 b ON a.number=b.number AND b.ACCOUNT='TAX' where a.Account='PAY'
  )

fiddle

number	ACCOUNT	COLOR
2	TAX	RED
2	TAX	GREEN

Answer 3

A generic implementation, useful when you do have more than "PAY" and "TAX" types of accounts. can be:

create table Table0A (id int, account varchar(10), color varchar(10));

insert into Table0A
(id, account, color)
values
(1,'tax', 'blue'),
(1,'pay', 'pink'),
(2,'tax', 'red'),
(2,'tax', 'green');

select id,count(distinct account) as x
from Table0A group by id;


select A.*
from Table0A A
join (select id,count(distinct account) as x from Table0A group by id) as B on B.id = A.id
where B.x = 1;

This way you can just pick up those records where there's only a single "type" of account and don't need to write down data possible values on your query.

Fiddle here