SQL Server 2019

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

CREATE TABLE #Product(
    [Id] [int] IDENTITY(1,1)  NOT NULL,
    [ProductNumber] [varchar](60) NOT NULL,
    [ProductType] [int] NOT NULL,
    [ProductName] [varchar](60) NOT NULL,
PRIMARY KEY(Id)
);
INSERT INTO [#Product]([ProductNumber], [ProductType], [ProductName])
 VALUES('A-000217', 1, 'stuff 13')
,('A-000217', 5, 'stuff 13')
,('A-000086', 5, 'stuff 2')
,('A-000086', 5, 'stuff 2')
,('A-001229', 1, 'stuff 20')
,('A-001146', 5, 'stuff 21')
,('A-001146', 1, 'stuff 21')
7 rows affected

select * 
from #Product p
where exists (
    select * from #Product p2
    where p2.ProductNumber = p.ProductNumber
    group by p2.productNumber
    having Min(p2.productType) = 1 and Max(p2.productType) = 5
);


Id
ProductNumber
ProductType
ProductName


1
A-000217
1
stuff 13

2
A-000217
5
stuff 13

6
A-001146
5
stuff 21

7
A-001146
1
stuff 21

Id	ProductNumber	ProductType	ProductName
1	A-000217	1	stuff 13
2	A-000217	5	stuff 13
6	A-001146	5	stuff 21
7	A-001146	1	stuff 21