sql - 基于聚合行生成一个值，我想在多个值上使用 CASE？

Question

我有以下形式的表格:

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

我想把它变成一个表格，如:

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

我最初的想法是做类似的事情

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

但这似乎是一种困惑的做法，因为当我真正在做的是设置比较时，没有真正的理由使用字符串聚合。有没有更好的方法来做到这一点？

Answer 1

假设一个吃肉的人，不管其他人可能不吃什么，都是杂食动物，而吃动物副产品(但不吃肉)的人是素食主义者，那么你可以做这样的事情:

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;