Median Aggregation in SQL Server

It is one of those standard interview questions: how would you calculate a Median() aggregation in SQL Server?

The usual answer involves temp tables (or if you are especially hip and modern, the OVER clause) combined with a couple of well-judged SELECT TOPs.

And, sure, all this is do-able, but there has to be a better way!

(To be honest, that "better way" really ought to be Microsoft building it into the actual product; but in the meantime...)

Data Academy* now supports a dbo.__Median() aggregation in TRANSFORM-it, which you can use just like COUNT() or SUM().

In Data Academy, this is as simple as setting the Group Function to:

dbo.__Median(#)

So next time someone asks you how to do a Median Aggregation in SQL Server, there is a new reply in town:

Use Data Academy!

--------
* SQL 2008 / 2008R2 versions only - sorry 2005 folks!