[dba-SQLServer] leveraging the power of the database versus code

Ok I need to generate a crosstab query (aka pivot table) using SQL server. The problem with SQL server is that it can't handle dynamically generated columns. In other words you would need to hard code the names of the columns which assumes that you know them beforehand. However, in most cases, the column values could not be determined ahead of time. Access of course handles this with great aplomb. But SQL server? Well...that's another story. :)

So...I found a way to do this utilizing a stored procedure that dynamically generates the SELECT statements and uses a temporary table to store the values. You'd think that would be the end of the story...BUT the dba balked at the proposed method since according to him SQL server was not designed to handle this sort of stuff and that the dynamically generated SELECT statements would be highly inefficient.

My question then to the group is this...

Is it not better to let SQL server handle the load and the processing on the server side rather than let the client side handle all that processing?

To take an example:

State	Product1	Product2	Product3
CA	1000		500		200
MA	900		200		450
NV	600		600		600

where Products are dynamically generated

The method I discovered to produce the desired results using a stored procedure worked. The suggestion I got was to handle all of that processing in code (outside of SQL server). Which means that in order to produce the desired results above, the code would need to grab the recordset from SQL server, and for each column generate the appropriate SELECT statement to return the values to either populate an in-memory array or post the results to a table from which it can read the final results.

Now...given that scenario isn't it more efficient to let SQL server handle all of that processing given that it handles all of the processing on the server side instead of client side basically resulting in less round trips from the web server to the SQL server?

I would appreciate feedback from you guys.





The information contained in this e-mail message and any file, document, previous e-mail message and/or attachment transmitted herewith is confidential and may be legally privileged. It is intended solely for the private use of the addressee and must not be disclosed to or used by anyone other than the addressee. If you receive this transmission by error, please immediately notify the sender by reply e-mail and destroy the original transmission and its attachments without reading or saving it in any manner.  If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of any of the information contained in or attached to this transmission is STRICTLY PROHIBITED. E-mail transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of email transmission. Users and employees of the e-mail system are expressly required not to make defamatory statements and not to infringe or authorize any infringement of copyright or any other legal right by email communications. Any such communication is contrary to company policy. The company will not accept any liability in respect of such communication.