May 2005 Blog Posts
By an anonymous SQL user (a guest blogger)
1. SQL is too complicated!
When I have multiple values in my columns, like "JAN,FEB,MAR" or “15,84,22” , SQL Server just doesn't get it. It's almost impossible to write SQL statements on tables like that! If I can manage to get any data out at all, it ends up being a 500 line SELECT statement!! What is up with that? And it also takes an act of God to successfully delete a value from those lists. Why does MS make things so complicated?
2. Datatype conversions!
What is the deal with data types? I just use...
Jumping back a bit, I thought I'd also post a bit of code for those still using good old ASP and/or ADO. This is a function writen in VBScript that works much in the same way as the GetRows() method of an ADO recordset -- it returns the entire contents of the recordset in a 2-dimensional array. The difference with this one, of course, is that it will also do a pivot for you.
As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.
read more...
In my last post, I spoke briefly about how I felt that in general crosstabbing data is something that a presentation layer should do and not the database. Consider the result of a crosstab operation -- the columns returned will vary depending on the data. There is pretty much no further manipulation you can do with that result in T-SQL; in the relational database world, the column names of our database objects should be constants and not continually changing as the data changes. Also, in T-SQL there is no easy way to dynamically pivot data, and even doing it with...
First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times!
read more...