ASP / ADO Pivot Function & Example

Sun May 15, 2005 by Jeff Smith

(For information about using this code click here.)

<%@ Language=VBScript %>

<%Option Explicit%>

<html>

<head>

</head>

<body>

dim con 'ADODB.Connection

dim com 'ADODB.Command

dim rsPivots, rsData ' ADODB.Recordsets

dim Vals ' this will hold the 2-dimensional results (just like ADO's GetRows)

set con = server.CreateObject("ADODB.Connection")

set com = server.CreateObject("ADODB.Command")

con.Open("Provider=SQLOLEDB.1;Server=(local);Database=Northwind;uid=xx;pwd=xx")

com.ActiveConnection = con

' this is our list of column values for the pivot:

com.CommandText = "Select distinct ProductName from PivotTest order by ProductName"

set rsPivots = com.Execute()

' this is the actual data we are pivoting:

com.CommandText = "Select * from PivotTest order by CustomerID"

set rsData = com.Execute()

' and here's all you need to do:

Vals = Pivot(rsData, "CustomerID","productName","Qty",rsPivots)

' now let's output the data. Remember the difference from ADO's GetRows() is that

' Vals(x,0) is the column header for column x.

<tr>

dim r,c

for c = 0 to ubound(Vals,1)

response.Write("<TH>" & Vals(c,0) & "</TH>")

</tr>

for r = 1 to ubound(vals,2)

response.Write("<TR>")

for c = 0 to ubound(vals,1)

response.Write("<TD> " & Vals(c,r) & "</TD>")

response.Write("</TR>")

</table>

</body>

</html>

public function Pivot(rsData, strRow, strColumn, strValue, rsColumnVals)

dim tmp

dim d

dim Cols, colCount, totalCols

dim i, offset, r

' Use GetRows() to return an 2-dimensional array of all pivot values;

' the result is Cols(0,x) = the x-th pivot value

Cols = rsColumnVals.getRows()

' we need to create a dictionary lookup between pivot values and column numbers:

set d = server.CreateObject("Scripting.Dictionary")

colCount = Ubound(Cols,2)

for i = 0 to ColCount

d.Add Cols(0,i), i

' the total # of columns in our resulting array will be:

totalCols = ColCount + rsData.fields.count-1

redim Result(totalCols,2000)

' in our Results array, elements at (x,0) will be the column names for column x:

' The 'offset' variable will keep track of the current column; when we are

' done, offset will equal the column index of the first of the pivot columns:

offset = 0

for i = 0 to rsData.fields.count-1

tmp = rsData.fields(i).name

'note: add case insensitivity here:

if (tmp <> strColumn ) and (tmp <> strValue) then

Result(offset,0) = tmp

offset = offset + 1

end if

' Now, starting at column 'offset', add the column headers as well:

for i = 0 to ColCount

Result(i + offset,0) = Cols(0,i)

tmp = "%firstrow%"

r = 0

' now, let's get to work:

do while not rsData.eof

if tmp <> rsData(strRow).value then

r = r + 1

' Assign non-pivoted values:

for i = 0 to offset-1

Result(i,r) = rsData(result(i,0)).value

tmp = rsData(strRow).value

end if

' Assign the current pivot value:

Result(offset + Cint(d.Item(rsData(strColumn).value)),r) = rsData(strValue).value

rsData.movenext

loop

' re-size our array so that it fits the # of rows perfectly:

redim preserve Result(totalCols,r)

' and return the result:

Pivot = Result

end function

Legacy Comments


Rob 2006-02-10	re: ASP / ADO Pivot Function & Example I am trying ths script but I am getting a Variable is undefined: 'Pivot' If I define it as a variable, I get a type mismatch. Could you shed some light on this? Rob

Jeff S 2006-02-10	re: ASP / ADO Pivot Function & Example Did you name your function something else? In VBScript, you need to assign a value to the name of the function to return a value, which is what we've done here. If your function is named something else, you must replace the assignment in the last line with the name of your function.

Craig 2006-08-23	re: ASP / ADO Pivot Function & Example Is there any example schema/data available for this code.

Christie Mason 2007-10-12	re: ASP / ADO Pivot Function & Example Error in "response.Write("<TD>&nbsp" & Vals(c,r) & "</TD>")" "&nbsp" should have semi colon at end " "

Jeff 2007-10-12	re: ASP / ADO Pivot Function & Example Thanks, Christie -- It's been updated!

Christie Mason 2007-10-23	re: ASP / ADO Pivot Function & Example I'm using this to present search results and just had a dickens of a time catching errors for a no results situation. I finally got something to work but must admit I don't know why it works and why what should have worked didn't work. Checking for "If rsData.EOF" didn't work to toggle displays for empty rsData so decided to add set rsData = com.Execute() 'CM used to check for empty rsData hasData = "" If not rsData.EOF Then hasData="yes" End If and then check contents of "hasData" for display toggle. But then I still received an error in the function if the recordset was empty. It's odd to me that the function is defined after the closing "html" tag and I couldn't figure out where "rsColumnVals" was defined, so finally preceded "cols"definition with If not rsColumnVals.EOF Then Cols = rsColumnVals.getRows() and ended the if before the end function.

mohamed hazem 2008-11-10	re: ASP / ADO Pivot Function &amp; Example hi 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

MattB 2009-02-19	re: ASP / ADO Pivot Function & Example I know this is a long short but I want to total each row and column any ideas?

CM 2009-10-12	re: ASP / ADO Pivot Function & Example First, I just wanted to repeat my gratitude for this information. I'm stilling using it and have expanded it to total across a row (easy add a td set to the header cols then embed an "isNumeric" check and calculate tot=tot+(vals(c,r)), then add a td set and response.write(tot)). Totals by column, grab the column header from Vals(c,0) and query. Plus I've even been able to change the color to red for negative numbers and link to details using Vals(c,0) and (vals(0,r)). I still have no idea what a "Scripting.Dictionary" is but this code has expanded my horizons more than any other code example I've seen in the past 15 years. THANK YOU

aj_coder 2010-09-23	re: ASP / ADO Pivot Function & Example Jeff, thanks for posting this function. We have four web servers and one database server so I look for examples that don't rely upon SQL to transform the data. Your pivot function worked great for us!!! With a little tweeking, I was able to add links, aggregate row totals and aggregate column totals. Again, thanks for posting this example; it works great!