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




Group by and select rows based on if value combinations exist
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
