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
ELSE IF @level=2
I am sure you get the idea. Well i wondered if could do something like this
,CASE WHEN @Level = 6 THEN AssignedHandler ELSE '' END AS AssignedHandler
,SUM(LedgerAmountGBP) AS LedgerAmountGBP
,SUM(Records) AS Records
,SLKTeam AS DashboardText
GROUP BY LegalEntity
,CASE WHEN @Level < 6 THEN AssignedHandler ELSE '' END
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.