CODEWITHSUNDEEP
sqlsql-server
waiwai933
waiwai933

Reputation: 14559

Produce a value based on aggregated rows where I want to CASE on multiple values?

I have a table of the following form:

| PersonId | Eats              |
|----------|-------------------|
| 1        | Meat              |
| 1        | Animal Byproducts |
| 1        | Vegetables        |
| 2        | Animal Byproducts |
| 2        | Vegetables        |
| 3        | Vegetables        |

And I want to turn it into a table like:

| PersonId | DietaryPreference  |
|----------|--------------------|
| 1        | Omnivore           |
| 2        | Vegetarian         |
| 3        | Vegan              |

My initial thought is do to something like

WITH cte AS (
    SELECT 
        PersonId, 
        STRING_AGG (Eats,',') WITHIN GROUP (ORDER BY Eats ASC) AS EatsConcat
    FROM MyTable
    GROUP BY PersonId
)
SELECT 
    PersonId,
    CASE EatsConcat
        WHEN 'Animal Byproducts,Meat,Vegetables' THEN 'Omnivore' 
        WHEN 'Animal Byproducts,Vegetables'      THEN 'Vegetarian'
        WHEN 'Vegetables'                        THEN 'Vegan'
    END AS DietaryPreference
FROM cte

but this seems like a messy way of doing it since there's no real reason to use string aggregration when what I'm really doing is set comparison. Is there a better way of doing this?

Upvotes: 4

Views: 56

Answers (3)

Martin Smith
Martin Smith

Reputation: 453453

You can also use just aggregation and no CASE.

The below prefixes each of the dietary preferences with a digit, so that the order of the selection hierarchy is also the lexicographic order and it can be used with MAX, and then finds the MAX and strips the digit off in the final SELECT

SELECT m.PersonId,
       SUBSTRING(MAX(V.DietaryPreference), 2, 8000) AS DietaryPreference
FROM   MyTable m
       JOIN (VALUES ('Meat','3Omnivore'),
                    ('AnimalByproducts','2Vegetarian'),
                    ('Vegetables', '1Vegan')) V(Eats, DietaryPreference)
         ON V.Eats = M.Eats
GROUP  BY m.PersonId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270181

You can use conditional aggregation to process the hierarchy of values:

select personid,
       coalesce(max(case when eats in ('Meat') then 'Omnivore' end),
                max(case when eats in ('Animal Byproducts') then 'Vegetarian' end),
                max(case when etas in ('Vegetables') then 'Vegan' end)
               ) as dietarypreference
from mytable
group by personid

Upvotes: 1

Thom A
Thom A

Reputation: 95689

Assuming that simply someone that eats meat, regardless of what others they might not, is an Omnivore, and someone that eats Animal Byproducts (but not meat) is a Vegetarian, then you could do something like this:

WITH CTE AS(
    SELECT V.PersonID,
           COUNT(CASE V.Eats WHEN 'Meat' THEN 1 END) AS Meat,
           COUNT(CASE V.Eats WHEN 'AnimalByproducts' THEN 1 END) AS AnimalByproducts,
           COUNT(CASE V.Eats WHEN 'Vegetables' THEN 1 END) AS Vegetables
    FROM (VALUES(1,'Meat'),
                (1,'AnimalByproducts'),
                (1,'Vegetables'),
                (2,'AnimalByproducts'),
                (2,'Vegetables'),
                (3,'Vegetables'))V(PersonID,Eats)
     GROUP BY V.PersonID)
SELECT C.PersonID,
       CASE WHEN C.Meat > 0 THEN 'Omnivore'
            WHEN AnimalByproducts > 0 THEN 'Vegetarian' 
            WHEN Vegetables > 0 THEN 'Vegan'
            ELSE 'Very hungry'
       END
FROM CTE C;

Upvotes: 1

Related Questions