Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

ASP / ADO Pivot Function & Example

(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>&nbsp;" & 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

%>

 

 

 

Print | posted on Sunday, May 15, 2005 8:55 PM |

Feedback

Gravatar

# ASP and ADO Pivots -- Old School Cross Tabs

5/15/2005 9:27 PM | Welcome to Jeff's Blog
Gravatar

# 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
2/10/2006 1:11 PM | Rob
Gravatar

# 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.
2/10/2006 1:40 PM | Jeff S
Gravatar

# re: ASP / ADO Pivot Function & Example

Is there any example schema/data available for this code.
8/23/2006 4:24 AM | Craig
Gravatar

# re: ASP / ADO Pivot Function & Example

Error in "response.Write("<TD>&nbsp" & Vals(c,r) & "</TD>")" "&nbsp" should have semi colon at end "&nbsp;"
10/12/2007 12:36 PM | Christie Mason
Gravatar

# re: ASP / ADO Pivot Function & Example

Thanks, Christie -- It's been updated!
10/12/2007 1:12 PM | Jeff
Gravatar

# 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.
10/23/2007 5:51 AM | Christie Mason
Gravatar

# re: ASP / ADO Pivot Function &amp;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
11/10/2008 3:42 AM | mohamed hazem
Gravatar

# re: ASP / ADO Pivot Function & Example

I know this is a long short but I want to total each row and column any ideas?
2/19/2009 10:08 AM | MattB
Gravatar

# 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
10/12/2009 12:34 PM | CM
Gravatar

# 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!
9/23/2010 12:37 PM | aj_coder
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET