How can I build a CASE statement in SQL that tells when there are multiple/different values in another column for a specific item?

Question

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

Essentially I want to change this:

Into this:

I'm thinking some sort of CASE statement 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 multiples. Thanks in advance for the help!

Answer 1

Here's a solution based on row_number.

Select Fruit
      ,case when max(rn) over (partition by Fruit order by Fruit) > 1 then 'Multiple' else Color end as Color
from  (
       select Fruit 
             ,Color
             ,row_number() over(partition by Fruit order by Fruit) as rn
       from t
       ) t

Fruit	Color
Apple	Multiple
Apple	Multiple
Banana	Yellow
Cherry	Red

Fiddle

Answer 2

Basically you can start with this and refine:

SELECT
  fruit,
  CASE WHEN count(color) > 1 THEN 'Multiple' ELSE MIN(color) AS color
FROM fruit_and_color_table
GROUP BY fruit;

Answer 3

I dont know if this is a huge overkill, but I would do it this way:

WITH color_count AS
(
SELECT
  fruit,
  count(fruit) as count
 FROM fruit_and_color_table
GROUP BY fruit
)
SELECT
a.fruit
,CASE
  WHEN b.count > 1 THEN 'Multiple'
  ELSE a.color END
FROM
fruit_and_color_table a
JOIN color_count b ON a.Fruit = b.Fruit