12,689,349 members (28,621 online)
Tip/Trick
alternative version

6.3K views
4 bookmarked
Posted

Grouping dynamically on different columns in single query

, 4 Jan 2013 CPOL
 Rate this:
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:

```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.

Share

 Architect 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

You may also be interested in...

 Pro Pro

 First Prev Next
 Idea : use cube notion and the .QueryByCube function NLips18-Jan-14 7:48 NLips 18-Jan-14 7:48
 Re: Idea : use cube notion and the .QueryByCube function RahulAJoshi27-Jan-14 18:31 RahulAJoshi 27-Jan-14 18:31
 Can you provide any example? I understood NOTHING. Dmitry Barovik10-Jan-13 20:50 Dmitry Barovik 10-Jan-13 20:50
 I guess I am pretty clear on what I am suggesting. RahulAJoshi11-Jan-13 4:02 RahulAJoshi 11-Jan-13 4:02
 Last Visit: 31-Dec-99 19:00     Last Update: 16-Jan-17 1:01 Refresh 1