http://www.homeandlearn.co.uk/net/nets4p2.html
Advertisements
Public Function PivotTable(ByVal As DataTable) As DataTable Dim dest As New DataTable("Pivoted" + .TableName) dest.Columns.Add(" ") Dim r As DataRow For Each r In .Rows dest.Columns.Add(r(0).ToString()) Next r Dim i As Integer For i = 0 To (.Columns.Count - 1) - 1 dest.Rows.Add(dest.NewRow()) Next i For i = 0 To dest.Rows.Count - 1 Dim c As Integer For c = 0 To dest.Columns.Count - 1 If c = 0 Then dest.Rows(i)(0) = .Columns((i + 1)).ColumnName Else dest.Rows(i)(c) = .Rows((c - 1))((i + 1)) End If Next c Next i dest.AcceptChanges() Return dest End Function 'PivotTable
Dim query As String = <a><![CDATA[ SELECT a.QuestionID FROM CR_Answers a INNER JOIN CR_Class c ON c.ClassID = a.ClassID INNER JOIN CR_Questions q ON q.QuestionID = a.QuestionID WHERE a.CourseID = 1 AND c.ActionPlan = 1 AND q.Q_Year = '11/12' AND q.Q_Term </a>.Value()