How to Create a Parameterised GROUP BY Without Using Dynam..

On Jul 2, 5:41 pm, "Charles Law" wrote:
> Hi all
>
> I have read Erland's treatise on dynamic-SQL, and now I want to avoid it
> like the plague. But one of the areas that still seems problematic is the
> GROUP BY clause.
>
> Depending on the parameters passed into my sproc, I want to group on
> different columns. What is the neatest way to do this without dynamic-SQL,
> if there is one, or the dirtiest if not?
>
> TIA
>
> Charles

I hope you realize that dynamic SQL could be much much faster if the
tables you select from are big and if there are proper indexes.
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

On Jul 2, 6:34 pm, "Charles Law" wrote:
> Hi Alex
>
> Amongst other reasons, I want to ditch dynamic-SQL so that the queries in my
> sprocs can be validated when I save them rather than at runtime. Dynamic SQL
> also causes problems for our web people, who use LINQ.
>
> Charles
>
> "Alex Kuznetsov" wrote in message
>
>
>
> > On Jul 2, 5:41 pm, "Charles Law" wrote:
> >> Hi all
>
> >> I have read Erland's treatise on dynamic-SQL, and now I want to avoid it
> >> like the plague. But one of the areas that still seems problematic is the
> >> GROUP BY clause.
>
> >> Depending on the parameters passed into my sproc, I want to group on
> >> different columns. What is the neatest way to do this without
> >> dynamic-SQL,
> >> if there is one, or the dirtiest if not?
>
> >> TIA
>
> >> Charles
>
> > I hope you realize that dynamic SQL could be much much faster if the
> > tables you select from are big and if there are proper indexes.

Fair enough, but sometimes generic one-size-fits-all queries perform
poorly as compared to specific ones which can take advantage of proper
indexes. To make sure that all your SQL is valid, you can run unit
tests against all the branches in your code issuing dynamic SQL, or
generate specific static SQL queries for every case. The benefit in
performance might be dramatic.
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

Hi Michael

I agree, but therefore I need to be able to do just as you say without using
dynamic-SQL. Do you know of a way?

Charles


"Michael Coles" <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote in
message
> There's a dependency between the SELECT clause and the GROUP BY clause, so
> I don't think it would do you much good to dynamically change the GROUP BY
> columns without also changing the SELECT clause columns to reflect the
> same columns.
>
> --
>
> ========
> Michael Coles
> "Pro SQL Server 2008 XML"
> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>
>
> "Charles Law" wrote in message
>
>> Hi all
>>
>> I have read Erland's treatise on dynamic-SQL, and now I want to avoid it
>> like the plague. But one of the areas that still seems problematic is the
>> GROUP BY clause.
>>
>> Depending on the parameters passed into my sproc, I want to group on
>> different columns. What is the neatest way to do this without
>> dynamic-SQL, if there is one, or the dirtiest if not?
>>
>> TIA
>>
>> Charles
>>
>>
>
>
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

Hi Charles,

I'm not familiar with a way to build dynamic SELECT clauses and dynamic
GROUP BY clauses while maintaining static FROM clauses, JOIN clauses, etc.,
without either building your query strings dynamically on the server or
building them dynamically on the client. Just for the record, if you're
dynamically selecting columns to group by based on user choices in the UI
I'm guessing that you're using aggregate functions as well on some other
columns in the query. Can you give some examples of the types of GROUP BYs
you want to perform and the aggregations you're planning to do?

--

========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/


"Charles Law" wrote in message

> Hi Michael
>
> I agree, but therefore I need to be able to do just as you say without
> using dynamic-SQL. Do you know of a way?
>
> Charles
>
>
> "Michael Coles" <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote in
> message
>> There's a dependency between the SELECT clause and the GROUP BY clause,
>> so I don't think it would do you much good to dynamically change the
>> GROUP BY columns without also changing the SELECT clause columns to
>> reflect the same columns.
>>
>> --
>>
>> ========
>> Michael Coles
>> "Pro SQL Server 2008 XML"
>> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>>
>>
>> "Charles Law" wrote in message
>>
>>> Hi all
>>>
>>> I have read Erland's treatise on dynamic-SQL, and now I want to avoid it
>>> like the plague. But one of the areas that still seems problematic is
>>> the GROUP BY clause.
>>>
>>> Depending on the parameters passed into my sproc, I want to group on
>>> different columns. What is the neatest way to do this without
>>> dynamic-SQL, if there is one, or the dirtiest if not?
>>>
>>> TIA
>>>
>>> Charles
>>>
>>>
>>
>>
>
>
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

Hi Alex

Amongst other reasons, I want to ditch dynamic-SQL so that the queries in my
sprocs can be validated when I save them rather than at runtime. Dynamic SQL
also causes problems for our web people, who use LINQ.

Charles


"Alex Kuznetsov" wrote in message

> On Jul 2, 5:41 pm, "Charles Law" wrote:
>> Hi all
>>
>> I have read Erland's treatise on dynamic-SQL, and now I want to avoid it
>> like the plague. But one of the areas that still seems problematic is the
>> GROUP BY clause.
>>
>> Depending on the parameters passed into my sproc, I want to group on
>> different columns. What is the neatest way to do this without
>> dynamic-SQL,
>> if there is one, or the dirtiest if not?
>>
>> TIA
>>
>> Charles
>
> I hope you realize that dynamic SQL could be much much faster if the
> tables you select from are big and if there are proper indexes.
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

Hi Michael

It's hard to give you exact details because the project this is connected
with is sensitive.

But, in general, in dynamic SQL I would have something like

set @sql = 'SELECT ' + @grouping + ', SUM(col1), SUM(col2)'
set @sql = @sql + ' FROM MyTable'
set @sql = @sql + ' GROUP BY ' + @grouping
set @sql = @sql + ' ORDER BY ' + @grouping

and then execute it. The @grouping is constructed earlier depending on
parameters passed in.

Does that make any sense?

Charles


"Michael Coles" <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote in
message
> Hi Charles,
>
> I'm not familiar with a way to build dynamic SELECT clauses and dynamic
> GROUP BY clauses while maintaining static FROM clauses, JOIN clauses,
> etc., without either building your query strings dynamically on the server
> or building them dynamically on the client. Just for the record, if you're
> dynamically selecting columns to group by based on user choices in the UI
> I'm guessing that you're using aggregate functions as well on some other
> columns in the query. Can you give some examples of the types of GROUP
> BYs you want to perform and the aggregations you're planning to do?
>
> --
>
> ========
> Michael Coles
> "Pro SQL Server 2008 XML"
> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>
>
> "Charles Law" wrote in message
>
>> Hi Michael
>>
>> I agree, but therefore I need to be able to do just as you say without
>> using dynamic-SQL. Do you know of a way?
>>
>> Charles
>>
>>
>> "Michael Coles" <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote
>> in message
>>> There's a dependency between the SELECT clause and the GROUP BY clause,
>>> so I don't think it would do you much good to dynamically change the
>>> GROUP BY columns without also changing the SELECT clause columns to
>>> reflect the same columns.
>>>
>>> --
>>>
>>> ========
>>> Michael Coles
>>> "Pro SQL Server 2008 XML"
>>> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>>>
>>>
>>> "Charles Law" wrote in message
>>>
>>>> Hi all
>>>>
>>>> I have read Erland's treatise on dynamic-SQL, and now I want to avoid
>>>> it like the plague. But one of the areas that still seems problematic
>>>> is the GROUP BY clause.
>>>>
>>>> Depending on the parameters passed into my sproc, I want to group on
>>>> different columns. What is the neatest way to do this without
>>>> dynamic-SQL, if there is one, or the dirtiest if not?
>>>>
>>>> TIA
>>>>
>>>> Charles
>>>>
>>>>
>>>
>>>
>>
>>
>
>
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

"Charles Law" wrote in message

> Hi Michael
>
> It's hard to give you exact details because the project this is connected
> with is sensitive.
>
> But, in general, in dynamic SQL I would have something like
>
> set @sql = 'SELECT ' + @grouping + ', SUM(col1), SUM(col2)'
> set @sql = @sql + ' FROM MyTable'
> set @sql = @sql + ' GROUP BY ' + @grouping
> set @sql = @sql + ' ORDER BY ' + @grouping
>
> and then execute it. The @grouping is constructed earlier depending on
> parameters passed in.
>
> Does that make any sense?
>
> Charles

Yes, and it's sort of what I expected. Let me give you a counter-example
with values for the @grouping filled in and you tell me if we're on the mark
here:

set @grouping = 'Sales_Month, Sales_Year, Sales_Person';
set @sql = 'SELECT ' + @grouping + ', SUM(Total_Sales), SUM(Qty_Sold)';

Or you might change @grouping to 'Sales_Year, Product_ID' or 'Sales_Year,
Sales_Country, Product_Model'.

There's a new feature in SQL 2008 called grouping sets that might help with
this to some degree, but it's not available on 2005 and before (except
stripped-down versions through the ROLLUP and CUBE clauses, but I don't
think these would be that useful for your situation). Honestly, with all
the slicing and dicing that your users expect I would actually consider
using Analysis Services because it's optimized for this type of analysis of
aggregations of your data. On the downside there's a learning curve
involved for your developers; on the plus side, it's free

--

========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

"Charles Law" wrote in message

> Hi Michael
>
> It's hard to give you exact details because the project this is connected
> with is sensitive.
>
> But, in general, in dynamic SQL I would have something like
>
> set @sql = 'SELECT ' + @grouping + ', SUM(col1), SUM(col2)'
> set @sql = @sql + ' FROM MyTable'
> set @sql = @sql + ' GROUP BY ' + @grouping
> set @sql = @sql + ' ORDER BY ' + @grouping
>
> and then execute it. The @grouping is constructed earlier depending on
> parameters passed in.
>
> Does that make any sense?

Of course your problem makes sense. But does the need for dynamic sql make
sense when the structure of a query is the same or similar for any number of
table(s)/column(s)? In this case the need for dynamic sql is based on the
prehistoric computer science of sql. There's no need in this case for
dynamic sql in a modern database system. Check these out:
Passing a table as a parameter
http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-par...ter.htm
Creating a Super Function
http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html

Sql Server is a perfectly fine data repository (storage) layer (I use it all
the time). But to use sql as an interface is questionable. And once you get
'it' you'll look back at the need for dynamic sql as analogous to people
justifying jumping off buildings so they could practice their landings. If
you insist on living in a crazy place you'll end up doing crazy things. And
for application development the sql interface can be one crazy place
If you're having some problems getting your head around the ideas expressed
in the articles I suggest going on YouTube and listening to Grace Slick muse
about White Rabbits

www.beyondsql.blogspot.com
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

>> Depending on the parameters passed into my stored procedure , I want to group on different columns. What is the neatest way to do this without dynamic-SQL, if there is one, or the dirtiest if not? <<

Since they are different groupings, they ought to be different
modules. Cohesion and all that good software engineering ..

However, I have used GROUP BY ROLLUP, then filtered the results to the
level of aggregation I wanted in a particular case. The GROUPING()
function is important for this trick. SQL Server is not as smart
about optimizing this as DB2, but it should work.
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

Hi Michael

Yes, that's just the sort of thing. We will certainly be looking at SQL
Server 2008 on our future projects, but too much is committed to V2005 on
this project, as you could imagine. I also think we are too far down the
line to switch to Analysis Services, but again a definite one to look at for
the future.

I guess we will stick with the dynamic-SQL for now, and live with downsides.

Cheers.

Charles


"Michael Coles" <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote in
message
> "Charles Law" wrote in message
>
>> Hi Michael
>>
>> It's hard to give you exact details because the project this is connected
>> with is sensitive.
>>
>> But, in general, in dynamic SQL I would have something like
>>
>> set @sql = 'SELECT ' + @grouping + ', SUM(col1), SUM(col2)'
>> set @sql = @sql + ' FROM MyTable'
>> set @sql = @sql + ' GROUP BY ' + @grouping
>> set @sql = @sql + ' ORDER BY ' + @grouping
>>
>> and then execute it. The @grouping is constructed earlier depending on
>> parameters passed in.
>>
>> Does that make any sense?
>>
>> Charles
>
> Yes, and it's sort of what I expected. Let me give you a counter-example
> with values for the @grouping filled in and you tell me if we're on the
> mark here:
>
> set @grouping = 'Sales_Month, Sales_Year, Sales_Person';
> set @sql = 'SELECT ' + @grouping + ', SUM(Total_Sales), SUM(Qty_Sold)';
>
> Or you might change @grouping to 'Sales_Year, Product_ID' or 'Sales_Year,
> Sales_Country, Product_Model'.
>
> There's a new feature in SQL 2008 called grouping sets that might help
> with this to some degree, but it's not available on 2005 and before
> (except stripped-down versions through the ROLLUP and CUBE clauses, but I
> don't think these would be that useful for your situation). Honestly,
> with all the slicing and dicing that your users expect I would actually
> consider using Analysis Services because it's optimized for this type of
> analysis of aggregations of your data. On the downside there's a learning
> curve involved for your developers; on the plus side, it's free
>
> --
>
> ========
> Michael Coles
> "Pro SQL Server 2008 XML"
> http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/
>
>
>
>
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

What you describe is actually a typical case for using SSAS. On top of that
you can also make great use of SSRS as the front end.

Why reinvent the wheel?

If nothing else, you can at least give SSAS a chance over the weekend.
You'll quickly see how much time you've already wasted.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

Hi Alex

I don't think we can go down the route of static SQL for each case, as there
are as many as six columns of which the user can choose any combination.
That would just be too many sprocs/queries to maintain. I will look at how
far I can extract the core query into a view to keep the dynamic-SQL down,
but as I replied to Michael, it looks like we are stuck with it for now.

Cheers.

Charles


"Alex Kuznetsov" wrote in message

> On Jul 2, 6:34 pm, "Charles Law" wrote:
>> Hi Alex
>>
>> Amongst other reasons, I want to ditch dynamic-SQL so that the queries in
>> my
>> sprocs can be validated when I save them rather than at runtime. Dynamic
>> SQL
>> also causes problems for our web people, who use LINQ.
>>
>> Charles
>>
>> "Alex Kuznetsov" wrote in message
>>
>>
>>
>> > On Jul 2, 5:41 pm, "Charles Law" wrote:
>> >> Hi all
>>
>> >> I have read Erland's treatise on dynamic-SQL, and now I want to avoid
>> >> it
>> >> like the plague. But one of the areas that still seems problematic is
>> >> the
>> >> GROUP BY clause.
>>
>> >> Depending on the parameters passed into my sproc, I want to group on
>> >> different columns. What is the neatest way to do this without
>> >> dynamic-SQL,
>> >> if there is one, or the dirtiest if not?
>>
>> >> TIA
>>
>> >> Charles
>>
>> > I hope you realize that dynamic SQL could be much much faster if the
>> > tables you select from are big and if there are proper indexes.
>
> Fair enough, but sometimes generic one-size-fits-all queries perform
> poorly as compared to specific ones which can take advantage of proper
> indexes. To make sure that all your SQL is valid, you can run unit
> tests against all the branches in your code issuing dynamic SQL, or
> generate specific static SQL queries for every case. The benefit in
> performance might be dramatic.
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam.. 

I think I should do that, but I don't want to upset myself too much!

Thanks.

Charles


"ML" wrote in message

> What you describe is actually a typical case for using SSAS. On top of
> that
> you can also make great use of SSRS as the front end.
>
> Why reinvent the wheel?
>
> If nothing else, you can at least give SSAS a chance over the weekend.
> You'll quickly see how much time you've already wasted.
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: How to Create a Parameterised GROUP BY Without Using Dynam..