StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Use a standard sql query (pivots are expensive in terms of performance) and create a custom pivot function in your server side code. Here are a couple of examples.</p> <pre><code>''' <summary> ''' Pivots columnX as new columns for the X axis (must be unique values) and the remaining columns as ''' the Y axis. Optionally can include columns to exclude from the Y axis. ''' </summary> ''' <param name="dt"></param> ''' <param name="columnX"></param> ''' <param name="columnsToIgnore"></param> ''' <returns>DataTable</returns> ''' <remarks></remarks> Public Shared Function Pivot(ByVal dt As DataTable, ByVal columnX As String, ByVal ParamArray columnsToIgnore As String()) As DataTable Dim dt2 As New DataTable() If columnX = "" Then columnX = dt.Columns(0).ColumnName End If 'Add a Column at the beginning of the table dt2.Columns.Add(columnX) 'Read all DISTINCT values from columnX Column in the provided DataTable Dim columnXValues As New List(Of String)() 'Create the list of columns to ignore Dim listColumnsToIgnore As New List(Of String)() If columnsToIgnore.Length > 0 Then listColumnsToIgnore.AddRange(columnsToIgnore) End If If Not listColumnsToIgnore.Contains(columnX) Then listColumnsToIgnore.Add(columnX) End If ' Add the X axis columns For Each dr As DataRow In dt.Rows Dim columnXTemp As String = dr(columnX).ToString() If Not columnXValues.Contains(columnXTemp) Then columnXValues.Add(columnXTemp) dt2.Columns.Add(columnXTemp) Else Throw New Exception("The inversion used must have unique values for column " + columnX) End If Next 'Add a row for each non-columnX of the DataTable For Each dc As DataColumn In dt.Columns If Not columnXValues.Contains(dc.ColumnName) AndAlso Not listColumnsToIgnore.Contains(dc.ColumnName) Then Dim dr As DataRow = dt2.NewRow() dr(0) = dc.ColumnName dt2.Rows.Add(dr) End If Next 'Complete the datatable with the values For i As Integer = 0 To dt2.Rows.Count - 1 For j As Integer = 1 To dt2.Columns.Count - 1 dt2.Rows(i)(j) = dt.Rows(j - 1)(dt2.Rows(i)(0).ToString()).ToString() Next Next Return dt2 End Function ''' <summary> ''' Can pivot any column as X, any column as Y, and any column as Z. Sort on X, sort on Y and optionally, the ''' values at the intersection of x and y (Z axis) can be summed. ''' </summary> ''' <param name="dt"></param> ''' <param name="columnX"></param> ''' <param name="columnY"></param> ''' <param name="columnZ"></param> ''' <param name="nullValue"></param> ''' <param name="sumValues"></param> ''' <param name="xSort"></param> ''' <param name="ySort"></param> ''' <returns>DataTable</returns> ''' <remarks></remarks> Public Shared Function Pivot(ByVal dt As DataTable, ByVal columnX As String, ByVal columnY As String, ByVal columnZ As String, _ ByVal nullValue As String, ByVal sumValues As Boolean, ByVal xSort As Sort, ByVal ySort As Sort) As DataTable Dim dt2 As New DataTable() Dim tickList As List(Of Long) = Nothing If columnX = "" Then columnX = dt.Columns(0).ColumnName End If 'Add a Column at the beginning of the table dt2.Columns.Add(columnY) 'Read all DISTINCT values from columnX Column in the provided DataTable Dim columnXValues As New List(Of String)() Dim cols As Integer = 0 For Each dr As DataRow In dt.Rows If dr(columnX).ToString.Contains("'") Then dr(columnX) = dr(columnX).ToString.Replace("'", "") End If If Not columnXValues.Contains(dr(columnX).ToString) 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(dr(columnX).ToString) End If Next 'Sort X if needed If Not xSort = Sort.None Then columnXValues = SortValues(columnXValues, xSort) End If 'Add columnX For Each s As String In columnXValues dt2.Columns.Add(s) Next 'Verify Y and Z Axis columns were 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 dt.Rows If dr(columnY).ToString.Contains("'") Then dr(columnY) = dr(columnY).ToString.Replace("'", "") End If If Not columnYValues.Contains(dr(columnY).ToString()) Then columnYValues.Add(dr(columnY).ToString()) End If Next ' Now we can sort the Y axis if needed. If Not ySort = Sort.None Then columnYValues = SortValues(columnYValues, ySort) End If 'Loop all Distinct ColumnY Values For Each columnYValue As String In columnYValues 'Create a new Row Dim drReturn As DataRow = dt2.NewRow() drReturn(0) = columnYValue Dim rows As DataRow() = dt.[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 dt2.Columns If dc.ColumnName = rowColumnTitle Then 'If sumValues, try to perform a Sum 'If sum is not possible due to value types, use the nullValue string If sumValues Then If IsNumeric(dr(columnZ).ToString) Then drReturn(rowColumnTitle) = Val(drReturn(rowColumnTitle).ToString) + Val(dr(columnZ).ToString) Else drReturn(rowColumnTitle) = nullValue End If Else drReturn(rowColumnTitle) = dr(columnZ).ToString End If End If Next Next dt2.Rows.Add(drReturn) Next Else Throw New Exception("The columns to perform inversion are not provided") End If 'if nullValue param was provided, fill the datable with it If nullValue <> "" Then For Each dr As DataRow In dt2.Rows For Each dc As DataColumn In dt2.Columns If dr(dc.ColumnName).ToString() = "" Then dr(dc.ColumnName) = nullValue End If Next Next End If Return dt2 End Function ''' <summary> ''' Sorts a list of strings checking to see if they are numeric or date types. ''' </summary> ''' <param name="list"></param> ''' <param name="srt"></param> ''' <returns></returns> ''' <remarks></remarks> Private Shared Function SortValues(ByVal list As List(Of String), ByVal srt As Sort) As List(Of String) Dim tickList As List(Of Long) = Nothing Dim dblList As List(Of Double) = Nothing ' Figure out how to sort columnX For Each s As String In list Dim colDate As Date = Nothing If Date.TryParse(s, colDate) Then tickList = New List(Of Long) Exit For End If Next Dim dateTicks As Long If Not tickList Is Nothing Then For Each s As String In list dateTicks = DateTime.Parse(s).Ticks If Not tickList.Contains(dateTicks) Then tickList.Add(dateTicks) End If Next If srt = Sort.DESC Then tickList.Sort() tickList.Reverse() ElseIf srt = Sort.ASC Then tickList.Sort() End If list.Clear() For Each lng As Long In tickList list.Add(New Date(lng).ToString("G")) Next Else Dim dbl As Double = Nothing For Each s As String In list If IsNumeric(s) Then dblList = New List(Of Double) End If Next If Not dblList Is Nothing Then 'Doubles or Integers For Each s As String In list dbl = Val(s) If Not dblList.Contains(dbl) Then dblList.Add(dbl) End If Next If srt = Sort.DESC Then dblList.Sort() dblList.Reverse() ElseIf srt = Sort.ASC Then dblList.Sort() End If list.Clear() For Each d As Double In dblList list.Add(d.ToString) Next Else 'Strings If srt = Sort.DESC Then list.Sort() list.Reverse() ElseIf srt = Sort.ASC Then list.Sort() End If End If End If Return list End Function </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload