How to build a CASE statement in SQL that identifies when there are multiple DIFFERENT values for a specific item?

Keena 0
Viewed 26

Here is a simple example of what I'm trying to achieve:

Essentially I want to take a data set like this:

raw data

Into this:

output

I'm thinking some sort of CASE statement to check for when there are multiple colors for any single fruit, THEN 'Multiple' ELSE "Color", but not sure how to build that logic to check for multiple different values for a single item. Thanks in advance for the help!

dbeaver case sql
2 Answers
declare @table table
( fruit varchar(250),
  color varchar(250)
  )

insert into @table (fruit, color)
values
('banana', 'yellow')
, ('banana', 'yellow')
, ('apple', 'red')
, ('apple', 'green')

select distinct t.fruit, case when d.color = 1 then t.color else 'multiple' end as color from @table t
left join
(
select fruit, count(distinct color) as color from @table
group by fruit
) d on d.fruit = t.fruit

That one works. Using count distinct to get the amount of colors, then a distinct on the outer select.

Robert Shannon0 
select   fruit
        ,case when count(distinct Color) > 1 then 'Multiple' else max(color) end as Color
from     t
group by fruit
fruit Color
Apple Multiple
Banana Yellow
Cherry Multiple
Orange Orange

Fiddle

DannySlor0
Related