Case Statement to Add a Column

Question

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

A join is unnecessary: `case when count(case when Fruit = 'apple' then 1 end) over (partition by ID) > 0 then 'yes' else 'no' end` — shawnt00, Jun 06 '22 at 16:08

Joel Coehoorn · Accepted Answer · 2022-06-06T02:55:47.203

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

Lukasz Szozda · Answer 2 · 2022-06-06T08:14:28.333

1

It could be achieved without self JOIN by using windowed COUNT_IF:

SELECT *, COUNT_IF(Fruit = 'apple') OVER(PARTITION BY ID) > 0 AS Apple
FROM tab;

Output:

edited Jun 06 '22 at 08:14

answered Jun 06 '22 at 07:56

Lukasz Szozda

162,964
23
234
275

score 1 · Answer 3 · answered Oct 16 '22 at 02:55

In a new asking of this same question with more columns,

the form Lukas advocates performs poorly, as the COUNT/SUM is done per row,

at which point do a join between the raw, and the aggregated results should perform better.

select *
FROM MyTable as a
natural join (
    SELECT c.id,
       iff(count_if(c.Fruit like 'apple') > 0, 'yes', 'no') as "Apple",
       --iff(count_if(c.Fruit like 'banana')  > 0, 'yes', 'no') as "Banana",
       --iff(count_if(c.Fruit like 'orange') > 0, 'yes', 'no') as "Orange"
    FROM MyTable as c
    GROUP BY 1
) as b

score 0 · Answer 4 · answered Jun 06 '22 at 03:13

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;

Case Statement to Add a Column

4 Answers4

Linked