Dynamic Grouping the TSQL way

Often times in reporting we find ourselves with a need to do dynamic grouping. This can be accomplished in several ways one way to do this would be to actually build this into the report with dynamic grouping in formulas. The other way to accomplish this is to actually change the grouping inside a stored procedure returning this to the report in an already grouped nature. This process allows SQL Server to do the grouping on the database server. There are obviously disadvantages and advantages to this, one disadvantage is that it puts more of a load on the SQL Server but it also moves some of the load off of their reporting server. In my testing it put very little impact on the SQL Server however it does have some overhead. And dynamically grouping SQL the first thing we have to do is pass in a parameter that tells us how we would like the grouping returned. I am going to refer to this parameter as @groupby. We can then reference this parameter in our select statement with a case statement and also within our group by clause. So now let's create our stored procedure.  For this demonstration I am going to use the adventureworksdw database. The following Script could be easily converted to a stored proc but for the purpose of simplicity I will just keep it a script.

DECLARE @Groupby varchar(50)

set @Groupby ='LName'

SELECT CASE WHEN @Groupby = 'LName' THEN c.LastName

WHEN @Groupby = 'FName' THEN c.Firstname

ELSE ''

END AS GroupColumn

,sum(s.[SalesAmount]) AS Amount

  FROM [FactInternetSales] s

  join DimCustomer c on c.CustomerKey=s.CustomerKey

    GROUP BY CASE WHEN @Groupby = 'LName' THEN c.LastName

WHEN @Groupby = 'FName' THEN c.Firstname

ELSE ''

END

SELECT c.LastName

 AS GroupColumn

,sum(s.[SalesAmount]) AS Amount

  FROM [FactInternetSales] s

  join DimCustomer c on c.CustomerKey=s.CustomerKey

  join DimProduct p on p.ProductKey=s.ProductKey

  GROUP BY  c.LastName

 This script uses the case function to switch grouping on the fly. This can also be done with the IF statement and many different selects with hard coded grouping.  The reason I like the above way is that by using it you can eliminate many lines of code. So how does this affect the query plan?

Plan With Case

Plan Without Case

Analyzing the Above we find that the major difference is the Hash Match this is because one extra column the Firstname is being pushed through until we get to the compute scalar (Case). So the result is a slight overhead but most of the time this is

negligible.