Group by and select rows based on if value combinations exist

Question

I have a table PetsTable:

Id	Type	key	value
1	"Cat"	10	5
1	"Cat"	9	2
2	"dog"	10	5
1	"Cat"	8	4
1	"Cat"	6	3
2	"dog"	8	4
2	"dog"	6	3
3	"Cat"	13	5
3	"Cat"	10	0
3	"Cat"	8	0

How to insert this data into a new table MyPets from PetsTable with these conditions:

• Group by Id
• Only select rows when in the group exists (key = 10 and value = 5) and (key = 8 and value = 4) and (key = 6 and value = 3)
• If exists key = 9, then mark hasFee = 1 else hasFee = 0

Final table should look like:

Id	Type	hasFee
1	"Cat"	1
2	"dog"	0

Answer 1

One approach is to use window functions to evaluate your conditions, which you can then apply as conditions using a CTE.

This creates the data you desire, its then trivial to insert into a table of your choice.

create table Test (Id int, [Type] varchar(3), [Key] int, [Value] int);

insert into Test (Id, [Type], [Key], [Value])
values
(1, 'Cat', 10, 5),
(1, 'Cat', 9,  2),
(2, 'Dog', 10, 5),
(1, 'Cat', 8,  4),
(1, 'Cat', 6,  3),
(2, 'Dog', 8,  4),
(2, 'Dog', 6,  3),
(3, 'Cat', 13, 5),
(3, 'Cat', 10, 0),
(3, 'Cat', 8,  0);

with cte as (
  select *
    , sum(case when [Key] = 10 and [Value] = 5 then 1 else 0 end) over (partition by Id) Cond1
    , sum(case when [Key] = 8 and [Value] = 4 then 1 else 0 end) over (partition by Id) Cond2
    , sum(case when [Key] = 6 and [Value] = 3 then 1 else 0 end) over (partition by Id) Cond3
    , sum(case when [Key] = 9 then 1 else 0 end) over (partition by Id) HasFee
  from Test
)
select Id, [Type], HasFee
from cte
where Cond1 = 1 and Cond2 = 1 and Cond3 = 1
group by Id, [Type], HasFee;

Returns:

Id	Type	HasFee
1	Cat	1
2	Dog	0

Note: If you provide your sample data in this format (DDL+DML) you make it much easier for people to assist.

db<>fiddle