Code Snippet
Private dg As DataGrid
Private dg2 As DataGrid
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
datatable = New DataTable()
datatable.Columns.Add("id")
datatable.Columns.Add("subject")
datatable.Columns.Add("grade")
datatable.Rows.Add("2006-01", "Math", "88")
datatable.Rows.Add("2006-01", "English", "87")
datatable.Rows.Add("2006-02", "English", "87")
datatable.Rows.Add("2006-02", "Math", "87")
Dim dataview As New DataView(datatable)
Dim id As String = "2006-01"
dataview.RowFilter = "id='" + id + "'" 'set your filter option here
Dim forPivot As DataTable = dataview.ToTable()
forPivot.Columns.Remove("id")
Dim temp As DataTable = PivotTable(forPivot) 'the temp table contains the data you need
dg = New DataGrid()
dg.Width = 300
dg.Height = 150
dg.DataSource = datatable
Me.Controls.Add(dg)
dg2 = New DataGrid()
dg2.Width = 300
dg2.Height = 150
dg2.DataSource = temp
dg2.Top = 150
Me.Controls.Add(dg2)
'......
Dim comm As New SqlCommand("insert into yourtable (id, math, english, science, history) values (@id, @math, @english, @science, @history)", conn)
comm.Parameters.Add("@id", SqlDbType.VarChar).Value = id
If temp.Columns("Math") IsNot Nothing Then
comm.Parameters.Add("@math", SqlDbType.VarChar).Value = temp.Rows(0).ItemArray.GetValue(temp.Columns.IndexOf("Math"))
Else
comm.Parameters.Add("@math", SqlDbType.VarChar).Value = ""
End If
'......
Try
conn.Open()
comm.ExecuteNonQuery()
conn.Close()
Catch
conn.Close()
End Try
End Sub
Private Function PivotTable(ByVal source As DataTable) As DataTable
Dim dest As New DataTable("Pivoted" + source.TableName)
dest.Columns.Add(" ")
For Each r As DataRow In source.Rows
dest.Columns.Add(r(0).ToString())
Next
For i As Integer = 0 To source.Columns.Count - 2
dest.Rows.Add(dest.NewRow())
Next
For r As Integer = 0 To dest.Rows.Count - 1
For c As Integer = 0 To dest.Columns.Count - 1
If c = 0 Then
dest.Rows(r)(0) = source.Columns(r + 1).ColumnName
Else
dest.Rows(r)(c) = source.Rows(c - 1)(r + 1)
End If
Next
Next
dest.AcceptChanges()
Return dest
End Function
End Class