Each order may include a hot or cold drink or both. As in the table below.

I want it to be labeled "both" if an order contains both types of drinks, otherwise, it should be labeled as it is. As in the table below. How should I write this query in SQL?

CodePudding user response：

As already suggested you can do a group by with a case expression, and you could do this in an outer apply, like this DBFiddle example

select o.orderID,
       o.ProductID,
       case when oo.coldcount > 0 and oo.hotcount > 0 then 'both' else o.type end
from   orders o
  outer apply ( select o2.orderID, 
                       sum (case when o2.type = 'cold drink' then 1 else 0 end) coldcount,
                       sum (case when o2.type = 'hot drink' then 1 else 0 end) hotcount
                from   orders o2
                where  o2.orderID = o.orderID
                group by o2.orderID
              ) oo

which results in this

CodePudding user response：

I think you can do this with a little bit of conditional aggregation, assuming the type descriptions are as described:

select orderID, case when Min(both) != Max(both) then 'both' else Min(type) end [Label]
from t
cross apply (values(case when type in('hot drink', 'cold drink') then type end))b(both)
group by orderID
order by orderID;

Test Fiddle