posted 6/10/2010 by MikeMollenhour - Views: [3222]
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
END
SELECT c.LastName
AS GroupColumn
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.
I never knew it was possible to dynamically group like this, I can definitely see its usefullness.
Thanks,