column header

There is one more quiet complex example out there

http://aspalliance.com/538


Sachi

sachiek,

is it okay if you help me convert my present data source to pivot format (I'm not familiar with the other command, don't know what value to place.)

table called employee
ID
Name
Age
Sex
Score



                       Private Function GenerateSource() As DataTable
                          Dim con As New SqlConnection("......")
                          Dim cmd As SqlCommand = con.CreateCommand()
                          cmd.CommandType = CommandType.StoredProcedure
                          cmd.CommandText = "SP_operator_id_name_desig_team"
                          cmd.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, Nothing))
                          Dim da As New SqlDataAdapter(cmd)
                          Dim ds As New DataSet()
                          da.Fill(ds,"DataSetName")
                          Return ds.Tables("DataSetName")
                          cmd.dispose
                          cmd.parameters.clear
                          con.close
   
                       End function

                       Sub Page_Load(Sender As Object, E As EventArgs)
                          If Not Page.IsPostBack Then
                               datagrid1.DataSource=GenerateSource().DefaultView
                          end if
                        end sub


-------------------------please help me fill in the blanks

      Public Function PivotTable([source] As DataTable) As DataTable
         Dim dest As New DataTable("Pivoted" + [source].TableName)
         
         ' create shchema (string columns) for the destination
         '            the first column is for the source column name
         dest.Columns.Add(" ")
         
         '      the remaining dest columns are from each source table row (1st column)
         Dim r As DataRow
         For Each r In  [source].Rows
            dest.Columns.Add(r(0).ToString()) ' assign each row the Product name (r[0])
         Next r
         ' now add one row to the dest table for each column in the source, except
         '  the first which is the Product, in our case
         Dim i As Integer
         For i = 0 To ([source].Columns.Count - 1) - 1
            dest.Rows.Add(dest.NewRow())
         Next i
         
         ' now move the source columns to their position in the dest row/cell matrix
         '  starting down the destination rows, and across the columns
         Dim r As Integer
         For r = 0 To dest.Rows.Count - 1
            Dim c As Integer
            For c = 0 To dest.Columns.Count - 1
               If c = 0 Then
                  dest.Rows(r)(0) = [source].Columns((r + 1)).ColumnName ' the Product name
               Else
                  dest.Rows(r)(c) = [source].Rows((c - 1))((r + 1))
               End If
            Next c
         Next r
         dest.AcceptChanges()
         Return dest
      End Function 'PivotTable