Dynamic Grouping

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Dynamic Grouping

Dynamic Grouping

Rate Topic

Display Mode

Topic Options

Author

Message

danielfountain

Posted Monday, January 28, 2013 4:28 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 494, Visits: 563

Hey all,

I had a query that i didnt like - as it was a pain to maintain. Basically there are 6 levels of grouping that can be requested by a stored proc. So i had

IF @level =1
begin
select ....
end
ELSE IF @level=2
begin
select .....
end

I am sure you get the idea. Well i wondered if could do something like this

                SELECT  metricind
                       ,LegalEntity
                       ,SLKBU
                       ,SLKDepartment
                       ,SLKTeam
                       ,CASE WHEN @Level = 6 THEN AssignedHandler ELSE '' END AS AssignedHandler        
                       ,Banding
                       ,BandingOrder
                       ,SUM(LedgerAmountGBP) AS LedgerAmountGBP
                       ,SUM(Records) AS Records
                       ,SLKTeam AS DashboardText
                FROM   DATATable                
                GROUP BY LegalEntity
                       ,SLKBU
                       ,SLKDepartment
                       , SLKTeam 
                       ,CASE WHEN @Level < 6 THEN AssignedHandler ELSE '' END
                       ,metricind
                       ,Banding
                       ,BandingOrder

Obviously i would then move onto other columns for 5-1.... but this was a test.

However i am getting back

Column 'DATATable.AssignedHandler' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Now i read a couple of forum posts which seem to suggest you can do what i have asked.

So my question - can you do this (was the forum poster incorrect) and if so what am i doing wrong.

Else the only other way i can think of is building the SQL query. Dont fancy that either TBH

Any help greatfully received.

Dan

Post #1412340

Gazareth

Posted Monday, January 28, 2013 5:30 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 1:53 AM
Points: 1,474, Visits: 2,342

Your CASE statements need to match in the select & group by clauses - you have @Level = 6 in the select, and @Level < 6 in the group by.

Post #1412366

« Prev Topic | Next Topic »

Permissions