March 10, 2016 at 6:52 am
I have a huge select statement that has to be repeated because the Group By changes at the four different levels. Is there a way to leave the SELECT portion intact and by a parameter change the group by. Here is a simple example
Select Sum(Amount)
From <Table>
Group by Monthkey
Select Sum(Amount)
From <Table>
Group by Monthkey, CorporateName
I'd like to say
Select Sum(Amount)
From <Table>
If @Parm1 = 1
Group by Monthkey
If @Parm1 = 2
Group by Monthkey, CorporateName
The statement in question is huge and took big for Dynamic SQL.
Thanks for the help
Steve
March 10, 2016 at 7:11 am
SW_Lindsay (3/10/2016)
I have a huge select statement that has to be repeated because the Group By changes at the four different levels. Is there a way to leave the SELECT portion intact and by a parameter change the group by. Here is a simple exampleSelect Sum(Amount)
From <Table>
Group by Monthkey
Select Sum(Amount)
From <Table>
Group by Monthkey, CorporateName
I'd like to say
Select Sum(Amount)
From <Table>
If @Parm1 = 1
Group by Monthkey
If @Parm1 = 2
Group by Monthkey, CorporateName
The statement in question is huge and took big for Dynamic SQL.
Thanks for the help
Steve
Not sure what you mean by "took big for Dynamic SQL". If you want to dynamically change the group by you will have to use dynamic sql to do this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 10, 2016 at 1:19 pm
I didn't test it, but I guess you could try to see if this works:
GROUP BY MonthKey, CASE WHEN @Parm1 = 2 THEN CorporateName END
I do not expect this to perform very well though. Dynamic SQL or repeating the query (preferably in separate stored procedures that are called from a master stored procedure) will almost certainly perform better.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply