Bryan Reynolds

Software, Business, Life . . .

Contact

Bryan Reynolds
  Bryan Reynolds Linked In
E-mail me Send mail

Recent comments

Archive

Authors

Tags

Categories


Blogroll

Download OPML file OPML

Recent posts

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2008

Datatable Pivot/Crosstab

This article was written to show an example of how to pivot or transform a ADO.Net datatable.  This can also be referred to as a crosstab query.  This example code below was written for a VB.NET application but you can easily convert this to C#.

These unit tests where written for the MS Unit testing framework within Visual Studio Team System 2008.

I am sure you have seen example of this type before, so I will be brief.  

I rewrote this with agile development and unit testing in mind.  Basically this code takes a datatable that you want to transform.  A key column that defines each unique row.  A variable column that will display across the top and a value column representing the data for it.

For this example to work you need to sort the table by that key column.  Alternatively, I could add some code to make sure that happens in the view.  Will probably update that later.

For those of you that are new to unit testing this was a perfect example of a time when testing came in handy.  When I tested the code without variable columns that where of type date the class worked great.  As soon as I change the data to a date field the unit test crashed.  Below is the result of the modified code by adding ".tostring"

 

Code for the PivotTable Class

 

   1: Namespace GEN.DL.Transformation
   2:  
   3:     Public Class PivotTable
   4:  
   5:         Private lSourceTable As DataTable
   6:         Private lVariableColumn As DataColumn
   7:         Private lAggregateColumn As DataColumn
   8:         Private lKeyColumn As DataColumn
   9:         Private lDestinationTable As New DataTable
  10:  
  11:  
  12: #Region "Public Properties"
  13:  
  14:         ''' <summary>
  15:         ''' Pivot datatable
  16:         ''' </summary>
  17:         Public Sub New(ByVal mSourceTable As DataTable, ByVal mKeyColumn As DataColumn, _
  18:                        ByVal mVariableColumn As DataColumn, ByVal mValueColumn As DataColumn)
  19:  
  20:             lSourceTable = mSourceTable
  21:             lKeyColumn = mKeyColumn
  22:             lVariableColumn = mVariableColumn
  23:             lAggregateColumn = mValueColumn
  24:  
  25:         End Sub
  26:  
  27: #End Region
  28:  
  29:         ''' <summary>
  30:         ''' Pivots a datatable
  31:         ''' </summary>
  32:         ''' <returns>Returns pivot datatable</returns>
  33:         Public Function Transform() As DataTable
  34:  
  35:             Dim mColumn As DataColumn ' Column used in for each to add non essential columns
  36:             Dim mSourceDataRow As DataRow ' Source Row
  37:             Dim mDestinationDataRow As DataRow = Nothing ' Row to be Added
  38:             Dim mCurrentKeyValue As String = "" ' Current Key Name
  39:             Dim mCurrentColumn As String = "" ' Current Column Name
  40:  
  41:  
  42:             ' Add All the columns excluding the aggregate and the Variable Column
  43:             For Each mColumn In lSourceTable.Columns
  44:  
  45:                 If mColumn.Ordinal <> lVariableColumn.Ordinal AndAlso _
  46:                    mColumn.Ordinal <> lAggregateColumn.Ordinal Then
  47:  
  48:                     lDestinationTable.Columns.Add(mColumn.ColumnName, mColumn.DataType)
  49:  
  50:                 End If
  51:             Next
  52:  
  53:             For Each mSourceDataRow In lSourceTable.Rows ' Main Process to add values to pivot table
  54:  
  55:                 ' Set the current Column Value
  56:                 mCurrentColumn = mSourceDataRow(lVariableColumn.Ordinal).ToString
  57:  
  58:                 If Not lDestinationTable.Columns.Contains(mCurrentColumn) Then ' Column is new
  59:  
  60:                     Dim lDataColumn As DataColumn = lDestinationTable.Columns.Add(mCurrentColumn, lAggregateColumn.DataType)
  61:  
  62:                     ' set the index so that it is sorted properly:
  63:                     Dim newOrdinal As Integer = lDataColumn.Ordinal
  64:                     Dim i As Integer
  65:  
  66:                     For i = newOrdinal - 1 To lSourceTable.Columns.Count - 2 Step -1
  67:                         If lDataColumn.ColumnName.CompareTo(lDestinationTable.Columns(i).ColumnName) < 0 Then
  68:                             newOrdinal = i
  69:                         End If
  70:                     Next
  71:                     lDataColumn.SetOrdinal(newOrdinal)
  72:  
  73:                 End If
  74:  
  75:                 If mCurrentKeyValue <> mSourceDataRow(lKeyColumn.Ordinal).ToString Then ' New Row
  76:  
  77:                     If Not mCurrentKeyValue = "" Then ' First row
  78:                         lDestinationTable.Rows.Add(mDestinationDataRow)
  79:                     End If
  80:  
  81:                     mCurrentKeyValue = mSourceDataRow(lKeyColumn.Ordinal).ToString
  82:                     mDestinationDataRow = lDestinationTable.NewRow()
  83:  
  84:                     For Each mColumn In lSourceTable.Columns
  85:  
  86:                         If mColumn.Ordinal <> lVariableColumn.Ordinal AndAlso _
  87:                            mColumn.Ordinal <> lAggregateColumn.Ordinal Then
  88:  
  89:                             mDestinationDataRow(mColumn.ColumnName) = mSourceDataRow(mColumn.ColumnName)
  90:  
  91:                         End If
  92:                     Next
  93:  
  94:                 End If
  95:  
  96:                 mDestinationDataRow(mCurrentColumn) = mSourceDataRow(lAggregateColumn.Ordinal)
  97:  
  98:             Next
  99:  
 100:             ' If rows where added, add the last row
 101:             If mCurrentKeyValue.Length > 0 Then lDestinationTable.Rows.Add(mDestinationDataRow)
 102:  
 103:             Return lDestinationTable
 104:  
 105:         End Function
 106:  
 107:     End Class
 108:  
 109: End Namespace

 

 

Unit Tests

 
   1: Imports System.Data
   2: Imports Microsoft.VisualStudio.TestTools.UnitTesting
   3: Imports GEN.DL.Transformation
   4:  
   5: '''<summary>
   6: '''This is a test class for PivotTableTest and is intended
   7: '''to contain all PivotTableTest Unit Tests
   8: '''</summary>
   9: <TestClass()> _
  10: Public Class PivotTableTest
  11:  
  12:  
  13:     Private testContextInstance As TestContext
  14:  
  15:     '''<summary>
  16:     '''Gets or sets the test context which provides
  17:     '''information about and functionality for the current test run.
  18:     '''</summary>
  19:     Public Property TestContext() As TestContext
  20:         Get
  21:             Return testContextInstance
  22:         End Get
  23:         Set(ByVal value As TestContext)
  24:             testContextInstance = value
  25:         End Set
  26:     End Property
  27:  
  28: #Region "Additional test attributes"
  29:     '
  30:     'You can use the following additional attributes as you write your tests:
  31:     '
  32:     'Use ClassInitialize to run code before running the first test in the class
  33:     '<ClassInitialize()>  _
  34:     'Public Shared Sub MyClassInitialize(ByVal testContext As TestContext)
  35:     'End Sub
  36:     '
  37:     'Use ClassCleanup to run code after all tests in a class have run
  38:     '<ClassCleanup()>  _
  39:     'Public Shared Sub MyClassCleanup()
  40:     'End Sub
  41:     '
  42:     'Use TestInitialize to run code before running each test
  43:     '<TestInitialize()>  _
  44:     'Public Sub MyTestInitialize()
  45:     'End Sub
  46:     '
  47:     'Use TestCleanup to run code after each test has run
  48:     '<TestCleanup()>  _
  49:     'Public Sub MyTestCleanup()
  50:     'End Sub
  51:     '
  52: #End Region
  53:  
  54:  
  55:     '''<summary>
  56:     '''A test for Transform
  57:     '''</summary>
  58:     <TestMethod()> _
  59:     Public Sub TransformTestGeneral()
  60:  
  61:         Dim mSourceTable As New DataTable("TestPivotTable")
  62:         Dim mKeyColumn As New DataColumn("Key", System.Type.GetType("System.DateTime"))
  63:         Dim mVariableColumn As New DataColumn("Variable", System.Type.GetType("System.DateTime"))
  64:         Dim mAggregateColumn As New DataColumn("Aggregate", System.Type.GetType("System.Int32"))
  65:         Dim mDataRow As DataRow
  66:  
  67:         mSourceTable.Columns.Add(mKeyColumn)
  68:         mSourceTable.Columns.Add(mVariableColumn)
  69:         mSourceTable.Columns.Add(mAggregateColumn)
  70:  
  71:         ' Row #1
  72:         mDataRow = mSourceTable.NewRow
  73:         mDataRow("Key") = "1/1/2008"
  74:         mDataRow("Variable") = "1/1/2008"
  75:         mDataRow("Aggregate") = 1
  76:         mSourceTable.Rows.Add(mDataRow)
  77:  
  78:         mDataRow = mSourceTable.NewRow
  79:         mDataRow("Key") = "1/1/2008"
  80:         mDataRow("Variable") = "1/2/2008"
  81:         mDataRow("Aggregate") = 2
  82:         mSourceTable.Rows.Add(mDataRow)
  83:  
  84:         mDataRow = mSourceTable.NewRow
  85:         mDataRow("Key") = "1/1/2008"
  86:         mDataRow("Variable") = "1/3/2008"
  87:         mDataRow("Aggregate") = 3
  88:         mSourceTable.Rows.Add(mDataRow)
  89:  
  90:         ' Row #2
  91:         mDataRow = mSourceTable.NewRow
  92:         mDataRow("Key") = "1/2/2008"
  93:         mDataRow("Variable") = "1/1/2008"
  94:         mDataRow("Aggregate") = 1
  95:         mSourceTable.Rows.Add(mDataRow)
  96:  
  97:         mDataRow = mSourceTable.NewRow
  98:         mDataRow("Key") = "1/2/2008"
  99:         mDataRow("Variable") = "1/2/2008"
 100:         mDataRow("Aggregate") = 2
 101:         mSourceTable.Rows.Add(mDataRow)
 102:  
 103:         mDataRow = mSourceTable.NewRow
 104:         mDataRow("Key") = "1/2/2008"
 105:         mDataRow("Variable") = "1/3/2008"
 106:         mDataRow("Aggregate") = 3
 107:         mSourceTable.Rows.Add(mDataRow)
 108:  
 109:         ' Row #3
 110:         mDataRow = mSourceTable.NewRow
 111:         mDataRow("Key") = "1/3/2008"
 112:         mDataRow("Variable") = "1/1/2008"
 113:         mDataRow("Aggregate") = 1
 114:         mSourceTable.Rows.Add(mDataRow)
 115: