I have the following table:
ID Fruit
A apple
A banana
A grapes
B orange
B apple
B grapes
C grapes
C orange
C banana
I would like to add a new column called Apple such to denote whether ID is associated with apple or not:
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
CodePudding user response:
Since this seems like a contrived example, I'll post several options. The best one will depend on what you're really doing.
First up, this is likely to perform best, but it risks duplicating rows if you could have multiple matches for the JOINed table. It's also the only solution I'm presenting to actually use a CASE
expression as requested.
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'
Alternatively, this will never duplicate rows, but has to re-run the nested query for each result row. If this is not a contrived example, but something more like homework, this is probably the expected result.
SELECT *, coalesce(
(
SELECT TOP 1 'Yes'
FROM MyTable b
WHERE b.ID = a.ID AND b.Fruit = 'Apple'
), 'No') As Apple
FROM MyTable a
Finally, this also re-runs the nested query for each result row, but there is the potential a future enhancement will improve on that and it makes it possible to provide values for multiple columns from the same nested subquery.
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
See them work here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e1991e8541e7421b90f601c7e8c8906b
CodePudding user response:
For the following table,
ID Fruit
A apple
A banana
A grapes
B orange
B apple
B grapes
C grapes
C orange
C banana
Adding a new column called Apple to denote whether ID is associated with apple or not, the resultset would be
ID Fruit Apple
A apple yes
A banana no
A grapes no
B orange no
B apple yes
B grapes no
C grapes no
C orange no
C banana no
If the expected resultset is as above, the below query will help to get the desired output.
select
id,
case
when fruit='apple' then 'yes'
when fruit!='apple' then 'no'
end as Apple
from Fruits;
CodePudding user response:
case when count(case when Fruit = 'apple' then 1 end) over (partition by ID) > 0 then 'Yes' else 'No' end