T-SQL Group By; Contains or IfAny

Question

SQL2005 and/or SQL2008 Is there any kind of built-in aggregate, within T-SQL, for Contains or IfAny or whatever? Something where any in the group equals a value? Similar to Max(xyz)=value except not limited to max.

Select custID, case when Min(ProductGroup)= "A" then 'Have Ordered Group A' else 'Haven't Ordered Group A' end hasOrdered
from orders
inner join products on ordPoductId = productID
group by custID

This works for a single value comparison, if it is min/max, but instead I want something like:

Select custID, case when contains(ProductGroup, "G") then 'Have Ordered Group G' else 'Haven't Ordered Group G' end hasOrdered
from orders
inner join products on ordPoductId = productID
group by custID

I could use Min(ProductGroup)="A" if the value I'm concerned about is a min/max or change the from-clause to (case when 'G' then 0 else 1 end) to create a fake maximum. Currently I am only concerned with a single value, but I would like something more intuitive and flexible if possible. Any ideas?

Answer 1

Your examples at the end are close to what I'd normally do. Something like:

CASE MAX(CASE WHEN ProductGroup = 'G' THEN 1 ELSE 0 END)
   WHEN 1 THEN 'Have Ordered'
   ELSE 'Haven''t ordered'
END

Where the inner CASE expression will obviously be evaluated against each row, whereas the outer CASE expression determines whether the inner expression ever succeeded.