Posted by: Red Souldier | June 22, 2009

ASP.NET Cross Tab / Pivot from Data Table

Sometimes when you working with the large data, you might want to display the data in pivot table / cross tab. Crystal report have a very nice wizard to make our life easier to create cross tab, but not in vb.net

For example you have data table like Table CompanyDetail populated from the SQL Select Query below

company_code PAY_AMOUNT tax_period_month
777 3000,0000 05
777 2000,0000 08
777 1000,0000 11
1010000 4000,0000 11
777 5000,0000 11

And you want to display into this order:

company_code 05 08 11
777 3000,0000 2000,0000 6000,0000
1010000     4000,0000

So Here is the step to convert Data Table data into pivot table
Step1. Create this function:

ColumnX = Is the Column that you want to make cross tab (In this example is tax_period_month)
ColumnY = Is the column that will be put in the left column (company_code)
ColumnZ = Is the total value from combining columnX and columnY (PAY_AMOUNT)


    Function GetInversedDataTable(ByVal table As DataTable, ByVal columnX As String, ByVal columnY As String, ByVal columnZ As String, ByVal nullValue As String, ByVal sumValues As Boolean) As DataTable
        'Create a DataTable to Return
        Dim returnTable As New DataTable()

        If columnX = "" Then
            columnX = table.Columns(0).ColumnName
        End If

        'Add a Column at the beginning of the table
        returnTable.Columns.Add(columnY)


        'Read all DISTINCT values from columnX Column in the provided DataTale
        Dim columnXValues As New List(Of String)()

        For Each dr As DataRow In table.Rows

            Dim columnXTemp As String = dr(columnX).ToString()
            If Not columnXValues.Contains(columnXTemp) Then
                'Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.
                columnXValues.Add(columnXTemp)
                returnTable.Columns.Add(columnXTemp)
            End If
        Next

        'Verify if Y and Z Axis columns re provided
        If columnY <> "" AndAlso columnZ <> "" Then
            'Read DISTINCT Values for Y Axis Column
            Dim columnYValues As New List(Of String)()

            For Each dr As DataRow In table.Rows
                If Not columnYValues.Contains(dr(columnY).ToString()) Then
                    columnYValues.Add(dr(columnY).ToString())
                End If
            Next

            'Loop all Column Y Distinct Value
            For Each columnYValue As String In columnYValues
                'Creates a new Row
                Dim drReturn As DataRow = returnTable.NewRow()
                drReturn(0) = columnYValue
                'foreach column Y value, The rows are selected distincted
                Dim rows As DataRow() = table.[Select]((columnY & "='") + columnYValue & "'")

                'Read each row to fill the DataTable
                For Each dr As DataRow In rows
                    Dim rowColumnTitle As String = dr(columnX).ToString()

                    'Read each column to fill the DataTable
                    For Each dc As DataColumn In returnTable.Columns
                        If dc.ColumnName = rowColumnTitle Then
                            'If Sum of Values is True it try to perform a Sum
                            'If sum is not possible due to value types, the value displayed is the last one read
                            If sumValues Then
                                Try
                                    drReturn(rowColumnTitle) = Convert.ToDecimal(drReturn(rowColumnTitle)) + Convert.ToDecimal(dr(columnZ))
                                Catch
                                    drReturn(rowColumnTitle) = dr(columnZ)
                                End Try
                            Else
                                drReturn(rowColumnTitle) = dr(columnZ)

                            End If
                        End If
                    Next
                Next

                returnTable.Rows.Add(drReturn)

            Next
        Else
            Throw New Exception("The columns to perform inversion are not provided")
        End If

        'if a nullValue is provided, fill the datable with it
        If nullValue <> "" Then
            For Each dr As DataRow In returnTable.Rows
                For Each dc As DataColumn In returnTable.Columns
                    If dr(dc.ColumnName).ToString() = "" Then
                        dr(dc.ColumnName) = nullValue
                    End If
                Next
            Next
        End If

        Return returnTable
    End Function

Step 2: Add gridview to the page and create connection to database to set data table:

 Dim conn As SqlConnection = Nothing
        Try
            Dim connString As String = "Data Source=;Initial Catalog=;Integrated Security=True"

            conn = New SqlConnection(connString)
            Dim query As String = "select company_code, PAY_AMOUNT, tax_period_month from ttSSP0 order by tax_period_month"
            Dim cmd As SqlCommand = New SqlCommand(query, conn)
            conn.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Dim dt As DataTable = New DataTable()
            Dim dt2 As DataTable = New DataTable()
            dt.Load(dr)
            dt2 = GetInversedDataTable(dt, "tax_period_month", "company_code", "PAY_AMOUNT", "", True)
            GridView1.DataSource = dt2
            GridView1.DataBind()
        Catch ex As SqlException
            ' handle error
        Catch ex As Exception
            ' handle error
        Finally
            conn.Close()
        End Try
Advertisements

Responses

  1. Very nice solution I really appericiate it. I want to add Grand Totals at both column and row level your more help will be a high end thanks

  2. what about gridview in aspx? can you provide the code because mine, the value not appear.. sorry my english not so good…

    • Hi there,
      The above example is using gridview, this is the code
      GridView1.DataSource = dt2
      GridView1.DataBind()

  3. The original author of GetInversedDataTable is felipesabino in the article at http://www.codeproject.com/KB/recipes/CsharpPivotTable.aspx

  4. This is a beautiful piece of code. Thank you for posting it. It was exactly what I needed.

    To make it run in ASP.net 2.0, make these changes in both the function and the sub procedure:

    1. Replace List with ArrayList. Thus,

    Dim columnYValues As New List(Of String)()

    becomes

    Dim columnYValues As New ArrayList

    2. Replace DataTable, DataRow, and DataColumn with Data.DataTable, Data.DataRow, and Data.DataColumn.

    3. Replace CommandBehavior.CloseConnection with System.Data.CommandBehavior.CloseConnection.

    That oughta do it.

    Thanks again for posting this!

  5. This is the best solution i found for now. Searched and tested a lot of code, but this one is the best.

    Thanks for sharing it with ‘the world’

  6. Really Very Wonderful Solution!!!

  7. thanx!!! a great article…
    i just would like to ask..
    in my project i need to replace the column z or sum value into the textbox…
    do you have any idea??

    • Im not sure about that, but maybe you could make the table datagrid column into the textbox first in edit template. Since this coding is only datatable, so i think it should not be a problem.

      Thanks

  8. Thanks
    However can you provide the same in C#?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: