10

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?

Sai Kalyan Kumar Akshinthala
  • 11,704
  • 8
  • 43
  • 67
Peter
  • 177
  • 2
  • 5
  • Another way to put it could be AnyLike(columns, 'RegularExpression'). That doesn't exist, so I still don't know a solution however. – Peter Oct 17 '11 at 06:35
  • possible duplicate of [Which is the least expensive aggregate function in the absence of ANY()](http://stackoverflow.com/questions/6060241/which-is-the-least-expensive-aggregate-function-in-the-absence-of-any) – gbn Oct 17 '11 at 06:49

1 Answers1

20

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • @wilsjd - I never nag people for accepts - if they stick around for a while and participate, they'll tend to get around to it eventually. If they don't stick around, nagging them is probably another reason for them to leave. And `peter` hasn't been seen for 2+ years, so I doubt this will ever be accepted. – Damien_The_Unbeliever Dec 10 '13 at 18:08
  • @wilsjd - my favourite example is [this one](http://stackoverflow.com/a/4603388/15498) - if `grady` ever turns up and changes their accepted answer, then all of a sudden the juxtaposition is ruined, and I'd hate that. – Damien_The_Unbeliever Dec 10 '13 at 19:57