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!

CodePudding user response：

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.

CodePudding user response：

select   fruit
        ,case when count(distinct Color) > 1 then 'Multiple' else max(color) end as Color
from     t
group by fruit  

Fiddle