Firstly, thanks to Allen White for hosting T-SQL Tuesday #25.
This month's T-SQL Tuesday is about T-SQL Tips and Tricks. I mulled over a couple of ideas I had and eventually settled on showing a way to do selective aggregates. Not because I necessarily think its a trick, though it sort of is, but because I keep seeing this done in other terrible ways (as we'll see).
Selective AggregatesSo what is a selective aggregate?
Well I'm not even sure that's an official name. I just made it up but it does describe the approach. Its best described with an example, and I'm going to use our old friend AdventureWorks (get it from CodePlex)
This is a very trivial example, but imagine we wanted to get an aggregate (we're going to use COUNT) of sales by month and year. Well that's quite easy:
select year(OrderDate) ,month(OrderDate) ,count(*) from Sales.SalesOrderHeader group by year(OrderDate) ,month(OrderDate) order by year(OrderDate) ,month(OrderDate)
Boom! And there you have it. But what if we wanted to have years across the top...just like your CFO asked so (s)he could compare year on year?