question

iamminime avatar image
iamminime asked

SQL grouping

Hi all, I have a question that I cannot solve If I have this table **NAME FRUIT** John Mango John Apple May Orange May Watermelon May Tangerine Sam Apple I want to see who has an apple and who doesn't So, I want the result be like **NAME APPLE** John Y May N Sam Y Do I have some simple SQL command to do that? I do not want function.. I tried many ways but don't know how to skip those unwanted rows Thanks a lot!
sql query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
This won't be very efficient, but it will work. SELECT DISTINCT t.Name, CASE WHEN EXISTS(SELECT * FROM theTable t2 WHERE t2.Name = t.Name AND Fruit='Apple') THEN 'Y' ELSE 'N' END AS HasApple FROM theTable t;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dery avatar image
Dery answered
you may try this: SELECT Name, Fruit FROM ( SELECT [Name] ,a.[Fruit] , ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY a.Fruit DESC) Rn FROM [dbo].[FruitTable] CROSS APPLY (SELECT CASE Fruit WHEN 'Apple' THEN 'Yes' ELSE 'No' END) a ([Fruit]) ) T WHERE Rn = 1;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.