Click here to Skip to main content
15,867,895 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Grouping dynamically on different columns in single query

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
4 Jan 2013CPOL 11.1K   4   4
Grouping dynamically on different columns in single query

Introduction

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

OR

Grouping on Employee hierarchy by Role
MD, CxO, GH, DH, Worker

OR

Grouping based on Time hierarchy
Hour, Minute, Sec, milisec

The SQL:

SQL
declare @Groupby int
set @Groupby = 2 

-- 1 shall do group by miliSec

-- 2 shall do group by Secs

-- 3 shall do group by Minute



Select 

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

From TimeDim

GroupBy Hour, Minute, Secs, milisec 

Points of Interest

Use this in code at places where on user selection we wanted to show summary reports.

History 

First version released.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
India India
I work as CTO for Orwell IT Solutions Pvt Ltd.
I like to read about new technologies and experiment, I am always watchful and also mentor newbies joined in organization. I have blog on wordpress.

http://rahulajoshi.wordpress.com

Comments and Discussions

 
SuggestionIdea : use cube notion and the .QueryByCube function Pin
NLips18-Jan-14 6:48
NLips18-Jan-14 6:48 
GeneralRe: Idea : use cube notion and the .QueryByCube function Pin
RahulAJoshi27-Jan-14 17:31
RahulAJoshi27-Jan-14 17:31 
QuestionCan you provide any example? I understood NOTHING. Pin
Dmitry Barovik10-Jan-13 19:50
Dmitry Barovik10-Jan-13 19:50 
AnswerI guess I am pretty clear on what I am suggesting. Pin
RahulAJoshi11-Jan-13 3:02
RahulAJoshi11-Jan-13 3:02 
Relevant example is already given in code, probably I will reiterate

User Visits are to be displayed based on user selection of criteria and criteria is like for specific year selection show by Year, Quarter, HalfYear, Month

Data is like
UserID | VisitDate | Year | Quarter | HalfYear | Month
1 | 01 Jan 2012 | 2012 | 1 | 1 | 1
2 | 01 Feb 2012 | 2012 | 1 | 1 | 2
3 | 01 Mar 2012 | 2012 | 1 | 1 | 3
4 | 01 Apr 2012 | 2012 | 2 | 1 | 4
5 | 01 May 2012 | 2012 | 2 | 1 | 5
6 | 01 Jun 2012 | 2012 | 2 | 1 | 6
7 | 01 Jul 2012 | 2012 | 3 | 2 | 7
8 | 01 Aug 2012 | 2012 | 3 | 2 | 8
1 | 01 Jan 2013 | 2013 | 1 | 1 | 1
2 | 01 Jan 2013 | 2013 | 1 | 1 | 1
3 | 01 Jan 2013 | 2013 | 1 | 1 | 1
4 | 01 Jan 2013 | 2013 | 1 | 1 | 1
5 | 01 Jan 2013 | 2013 | 1 | 1 | 1

Based on user selection you want to show data for User Visits
-- for filtering user wants to do group by what?
declare @Groupby int

-- for filtering data
Declare @Quarter int, @Year int, @Halfyear int, @Month int

set @Groupby = 2

-- 1 shall do group by Month + HalfYear + Quarter + Year
-- 2 shall do group by HalfYear + Quarter + Year (Month is set to 'All')
-- 3 shall do group by Quarter + Year (Month and HalfYear is set to 'All')
-- 4 shall do group by Year (Month and HalfYear and Quarter is set to 'All')
-- 5 shall do group by Month only and rest all fields are set to 'All'
-- 6 shall do group by HalfYear only and rest all fields are set to 'All'
-- 7 shall do group by Quarter only and rest all fields are set to 'All'
-- 8 shall do group by Year only and rest all fields are set to 'All'

above grouping you need to do along with filtering on appropriate field i.e. Year+Quarter+HalfYear+Month

-- only one change I did in below query compared to my initial TIP

Select Year, Quarter, HalfYear, Month,NoOfVisits from
(Select
case when @Groupby <= 4 or @Groupby = 8 then Year else 'All' end as Year,
case when @Groupby <= 3 or @Groupby = 7 then Quarter else 'All' end as Quarter,
case when @Groupby <= 2 or @Groupby = 6 then HalfYear else 'All' end as HalfYear,
case when @Groupby <= 1 or @Groupby = 5 then Month else 'All' end as Month
,Count(UserID) as NoOfVisits
From TimeDim
Where Year = IsNull(@Year, Year) and Quarter = IsNull(@Quarter, Quarter)
and HalfYear = IsNull(@HalfYear, HalfYear) and Month = IsNull(@Month, Month)
)a
GroupBy Year, Quarter, HalfYear, Month

I guess I am pretty clear on what I suggest.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.