Imports Microsoft.
VisualBasic
Imports System
Imports [Link]
Imports [Link]
Imports [Link]
Imports [Link]
' Written by Anurag Gandhi.
' Url: [Link]
' Contact me at: [Link]@[Link]
''' <summary>
''' Pivots the data
''' </summary>
Public Class Pivot
Private _SourceTable As New DataTable()
Public Sub New(ByVal SourceTable As DataTable)
_SourceTable = SourceTable
End Sub
''' <summary>
''' Pivots the DataTable based on provided RowField, DataField, Aggregate
Function and ColumnFields.//
''' </summary>
''' <param name="RowField">The column name of the Source Table which you want
to spread into rows</param>
''' <param name="DataField">The column name of the Source Table which you want
to spread into Data Part</param>
''' <param name="Aggregate">The Aggregate function which you want to apply in
case matching data found more than once</param>
''' <param name="ColumnFields">The List of column names which you want to
spread as columns</param>
''' <returns>A DataTable containing the Pivoted Data</returns>
Public Function PivotData(ByVal RowField As String, ByVal DataField As String,
ByVal Aggregate As AggregateFunction, ByVal ParamArray ColumnFields As String()) As
DataTable
Dim dt As New DataTable()
Dim Separator As String = "."
Dim RawRowList = (From x In _SourceTable.AsEnumerable() Select New With
{.Name = [Link](Of Object)(RowField).ToString()}).Distinct()
Dim RowListParam As String() = (From s In RawRowList Select
[Link]).ToArray()
Dim RowList = GetDistinct(RowListParam)
' Gets the list of columns .(dot) separated.
Dim RawColList = (From x In _SourceTable.AsEnumerable() _
Select New With {.Name = [Link](Function(n)
[Link](Of Object)(n).ToString()) _
.Aggregate(Function(a, b) (a &
Separator & [Link]()))}).Distinct() _
.OrderBy(Function(x) [Link])
Dim ColListParam As String() = (From s In RawColList Select
[Link]).ToArray()
Dim ColList = GetDistinct(ColListParam)
[Link](RowField)
For Each col In ColList
' Cretes the result columns.//
If Not [Link]([Link]()) Then
[Link]([Link]())
End If
Next
For Each RowName In RowList
Dim row As DataRow = [Link]()
row(RowField) = [Link]()
For Each col In ColList
Dim strFilter As String = (RowField & " = '") + RowName & "'"
Dim strColValues As String() =
[Link]().Split([Link](), [Link])
For i As Integer = 0 To ([Link] - 1)
strFilter = strFilter & " and " & ColumnFields(i) & " = '" &
strColValues(i) & "'"
Next
row([Link]()) = GetData(strFilter, DataField, Aggregate)
Next
[Link](row)
Next
Return dt
End Function
'Private Function GetColList(ByVal ColumnFields As String()) As String()
' Dim RawColList = (From x In _SourceTable.AsEnumerable() _
' Select New With {.Name = [Link](Function(n)
[Link](Of Object)(n).ToString()) _
' .Aggregate(Function(a, b) a = a & Separator &
[Link]())}).OrderBy(Function(x) [Link])
' Dim ColList1 As String() = (From s In RawColList Select [Link]).ToArray()
' Dim ColList = GetDistinct(ColList1)
'End Function
Private Function GetDistinct(ByVal strList As String()) As String()
Dim NewList As List(Of String) = New List(Of String)()
For Each myStr In strList
If Not [Link](myStr) Then
[Link](myStr)
End If
Next
GetDistinct = [Link]()
End Function
''' <summary>
''' Retrives the data for matching RowField value and ColumnFields values with
Aggregate function applied on them.
''' </summary>
''' <param name="Filter">DataTable Filter condition as a string</param>
''' <param name="DataField">The column name which needs to spread out in Data
Part of the Pivoted table</param>
''' <param name="Aggregate">Enumeration to determine which function to apply to
aggregate the data</param>
''' <returns></returns>
Private Function GetData(ByVal Filter As String, ByVal DataField As String,
ByVal Aggregate As AggregateFunction) As Object
Try
Dim FilteredRows As DataRow() = _SourceTable.[Select](Filter)
Dim objList As Object() = FilteredRows.[Select](Function(x) [Link](Of
Object)(DataField)).ToArray()
Select Case Aggregate
Case [Link]
Return GetAverage(objList)
Case [Link]
Return [Link]()
Case [Link]
Return If(([Link]() = 0), "False", "True")
Case [Link]
Return GetFirst(objList)
Case [Link]
Return GetLast(objList)
Case [Link]
Return GetMax(objList)
Case [Link]
Return GetMin(objList)
Case [Link]
Return GetSum(objList)
Case Else
Return Nothing
End Select
Catch ex As Exception
Return "#Error"
End Try
Return Nothing
End Function
Private Function GetAverage(ByVal objList As Object()) As Object
Return If([Link]() = 0, Nothing,
DirectCast(([Link](GetSum(objList)) / [Link]()), Object))
End Function
Private Function GetSum(ByVal objList As Object()) As Object
Return If([Link]() = 0, Nothing, DirectCast(([Link](New
Decimal(), Function(x, y) x + [Link](y))), Object))
End Function
Private Function GetFirst(ByVal objList As Object()) As Object
Return If(([Link]() = 0), Nothing, [Link]())
End Function
Private Function GetLast(ByVal objList As Object()) As Object
Return If(([Link]() = 0), Nothing, [Link]())
End Function
Private Function GetMax(ByVal objList As Object()) As Object
Return If(([Link]() = 0), Nothing, [Link]())
End Function
Private Function GetMin(ByVal objList As Object()) As Object
Return If(([Link]() = 0), Nothing, [Link]())
End Function
End Class
Public Enum AggregateFunction
Count = 1
Sum = 2
First = 3
Last = 4
Average = 5
Max = 6
Min = 7
Exists = 8
End Enum
