Dynamic grouping is a big problem when you want to show grouping
on different columns in the same query so either you need to write different
queries or do some kind of dynamic query; instead of that, the solution I describe
below will help to dynamically manipulate results.
Using the code
Grouping on different columns of a table incrementally by its hierarchy but conditionally, i.e.,
Grouping on organization hierarchy
Enterprise, Group, Company, RO, Branch
Grouping on Employee hierarchy by Role
MD, CxO, GH, DH, Worker
Grouping based on Time hierarchy
Hour, Minute, Sec, milisec
declare @Groupby int
set @Groupby = 2
case when @Groupby <= 4 then Hour else 'All' end as Hour,
case when @Groupby <= 3 then Minute else 'All' end as Minute,
case when @Groupby <= 2 then Secs else 'All' end as Secs,
case when @Groupby <= 1 then milisec else 'All' end as milisec
Sum(Cases) as NoOfCases
GroupBy Hour, Minute, Secs, milisec
Points of Interest
Use this in code at places where on user selection we wanted to show summary reports.
First version released.