SQL Server 2019

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

create table MyTable (ID char(1), Fruit varchar(10));
insert into MyTable (ID, Fruit) values
('A', 'apple'),
('A', 'banana'),
('A', 'grapes'),
('B', 'orange'),
('B', 'apple'),
('B', 'grapes'),
('C', 'grapes'),
('C', 'orange'),
('C', 'banana');
9 rows affected

SELECT a.*, case when b.ID IS NOT NULL THEN 'Yes' ELSE 'No' END AS Apple
FROM MyTable a
LEFT JOIN MyTable b on b.ID = a.ID AND b.Fruit = 'Apple'


ID
Fruit
Apple


A
apple
Yes

A
banana
Yes

A
grapes
Yes

B
orange
Yes

B
apple
Yes

B
grapes
Yes

C
grapes
No

C
orange
No

C
banana
No


SELECT *, coalesce(
    (
       SELECT TOP 1 'Yes' 
       FROM MyTable b 
       WHERE b.ID = a.ID AND b.Fruit = 'Apple'
    ), 'No') As Apple
FROM MyTable a


ID
Fruit
Apple


A
apple
Yes

A
banana
Yes

A
grapes
Yes

B
orange
Yes

B
apple
Yes

B
grapes
Yes

C
grapes
No

C
orange
No

C
banana
No


SELECT a.*, COALESCE(c.Apple, 'No') Apple
FROM MyTable a
OUTER APPLY (
    SELECT TOP 1 'Yes' As Apple
    FROM MyTable b
    WHERE b.ID = a.ID AND b.Fruit = 'Apple'
) c


ID
Fruit
Apple


A
apple
Yes

A
banana
Yes

A
grapes
Yes

B
orange
Yes

B
apple
Yes

B
grapes
Yes

C
grapes
No

C
orange
No

C
banana
No

ID	Fruit	Apple
A	apple	Yes
A	banana	Yes
A	grapes	Yes
B	orange	Yes
B	apple	Yes
B	grapes	Yes
C	grapes	No
C	orange	No
C	banana	No