Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

ASP and ADO Pivots – Old School CrossTabs

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

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. 

Legacy Comments

Christie Mason
re: ASP and ADO Pivots -- Old School CrossTabs
Bless you, I could figure out the procedure to get the pivot accomplished but was having much difficulty determining how to present a dynamic table based on the results.

mohamed hazem
re: ASP and ADO Pivots -- Old School CrossTabs
i am using sql 2000 i have a problem,
i have this data on a text file:

name mohamed
birth 1980
job engineer

name hassan
birth 1978
job doctor

name .......

i want to import it via DTS to an sql table "with a format" like this:
name birth job
mohamed 1980 engineer
hassan 1978 doctor

please help me,
what can i do ???
i have a hudge problem at my work,
Thanks a lot
Eng. Moahmed Hazem

re: ASP and ADO Pivots -- Old School CrossTabs
I use temp tables to overcome the varchar(MAX) limit

declare @returnXML xml;

declare @tab2 table (i int IDENTITY(1,1) NOT NULL ,ItemXML xml NULL);

set @returnXML = (select UG.*,VT.AssassinA,VT.AssassinD,VT.Attack,VT.Defense,VT.DriveByA,VT.DriveByD,VT.GTAA,VT.GTAD,VT.BuyItemsID,VT.Icon,VT.RobA,VT.RobD,VT.WeaponName,VT.Image from UserGuns UG inner join WeaponsTypes VT on UG.GunID = VT.WeaponID where UG.UserID = 1 FOR XML PATH ('weapon'), ELEMENTS);

insert into @tab2 (ItemXML) VALUES(@returnXML);

set @returnXML = (select UV.*,VT.AssassinA,VT.AssassinD,VT.Attack,VT.Defense,VT.DriveByA,VT.DriveByD,VT.GTAA,VT.GTAD,VT.BuyItemsID,VT.Icon,VT.RobA,VT.RobD,VT.CarName,VT.Image from UserVehicles UV inner join VehicleTypes VT on UV.CarID = VT.CarID where UV.UserID = 1 FOR XML PATH ('vehicle'), ELEMENTS);

insert into @tab2 (ItemXML) VALUES(@returnXML);

select ItemXML from @tab2 FOR XML PATH (''), ELEMENTS;