(For information about using this code click here.)
<%@ Language=VBScript %>
<%Option Explicit%>
<html>
<head>
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
</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.
%>
<table cellpadding=0 cellspacing=0 border="black">
<tr>
<%
dim r,c
for c = 0 to ubound(Vals,1)
response.Write("<TH>" & Vals(c,0) & "</TH>")
next
%>
</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>")
next
response.Write("</TR>")
next
%>
</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
next
' 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
next
' Now, starting at column 'offset', add the column headers as well:
for i = 0 to ColCount
Result(i + offset,0) = Cols(0,i)
next
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
next
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
%>