OK, so let's sum up my recent flury of crosstab / pivoting posts the past week or so:

• Here's a dynamic way to do crosstabs in SQL Server
• But here's an alternate way to do this at the presentation layer in .NET
• And here's a performance comparison

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.  

An entire sample ASP page with the function embedded in it for you to enjoy can be found here.  This example, like the others, uses the Northwind database and requires that you have created the view defined here. 

Here are the parameters and expected data types of this “old school” Pivot function:

• rsData -- (ADODB.Recordset) This is the ADO recordset which contains the data to be pivoted. As before, it must be fully grouped, summarized and sorted properly.
• strRow - (String) This is the name of the field that can determines the Rows in the pivot table
• strColumn -- (String)  The name of the field that will be pivoted into columns
• strValue --  (String) The the name of the field that contains the summarized data to be pivoted into columns.
• rsColumnVals -- (ADODB.Recordset) This is a new one -- pass in a recordset containing a single column of vals for which to create the pivoted columns.

The last parameter is a new one, and it makes things as efficient as possible to get the list of column values ahead of time.  This is similiar to what the dynamic stored procedure method does.  Typically, if you are pivoting on field “XYZ”, then you will pass in a recordset that is defined along the lines of “SELECT DISTINCT XYZ FROM ...” for the rsColumnVals parameter.  Another thing to note is that the first row of values returned by this function will contain the column headers for the result, which is necessary since those column headers are unknown at runtime unlike when using GetRows().   See the comments and the example for more details.

As before, this method, even when compared to the ultra-speedy GetRows() method in ADO, is clearer, faster, and more efficient than the dynamic sql technique.   The main benefit, of course, is that you can use standard SQL with normal grouping and summarizing and you are just returning the data from the database, and not worrying about formatting the results into columns for display purposes.

Feel free to use or modify my code as needed; it is not designed to be a complete ready-to-use libary, but rather an example of ways you can create your own functions at the presentation layer in your applications to do things that the database layer should not have to worry about.