SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table #stockyes
(
PartNumber varchar(100),
sourcetype varchar(100),
stockid tinyint
)
insert into #stockyes(PartNumber,sourcetype,stockid)
values
(1233,'Website',1),
(1233,'Website',0),
(1233,'Datasheet',1),

(8901,'Website',1),
(8901,'Website',0),

(9015,'Website',1),

(7801,'Website',1),
(7801,'Website',1),

(9401,'Datasheet',0),

(5688,'Datasheet',0),
(5688,'Datasheet',0),

(5688,'Lifecycle',2),
(5688,'Lifecycle',2),

(8409,'Lifecycle',1),
(6703,'Website',0),
(6703,'Website',0),
(6703,'Website',0),

(3099,'Website',0),
(3099,'Website',0),

(8541,'Website',0)
20 rows affected

SELECT * FROM #stockyes;


PartNumber
sourcetype
stockid


1233
Website
1

1233
Website
0

1233
Datasheet
1

8901
Website
1

8901
Website
0

9015
Website
1

7801
Website
1

7801
Website
1

9401
Datasheet
0

5688
Datasheet
0

5688
Datasheet
0

5688
Lifecycle
2

5688
Lifecycle
2

8409
Lifecycle
1

6703
Website
0

6703
Website
0

6703
Website
0

3099
Website
0

3099
Website
0

8541
Website
0


select PartNumber,sourcetype,stockid
from #stockyes s
where exists (select null from #stockyes s1 where s.PartNumber=s1.PartNumber and s1.sourcetype ='Website')
and exists (select null from #stockyes s2 where s.PartNumber=s2.PartNumber and s2.stockid =1);


PartNumber
sourcetype
stockid


1233
Website
1

1233
Website
0

1233
Datasheet
1

8901
Website
1

8901
Website
0

9015
Website
1

7801
Website
1

7801
Website
1

PartNumber	sourcetype	stockid
1233	Website	1
1233	Website	0
1233	Datasheet	1
8901	Website	1
8901	Website	0
9015	Website	1
7801	Website	1
7801	Website	1
9401	Datasheet	0
5688	Datasheet	0
5688	Datasheet	0
5688	Lifecycle	2
5688	Lifecycle	2
8409	Lifecycle	1
6703	Website	0
6703	Website	0
6703	Website	0
3099	Website	0
3099	Website	0
8541	Website	0