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