Bryan Reynolds

Software, Business, Life . . .

Contact

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

Recent comments

Authors

Disclaimer

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

© Copyright 2014

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:  
 116:         mDataRow = mSourceTable.NewRow
 117:         mDataRow("Key") = "1/3/2008"
 118:         mDataRow("Variable") = "1/2/2008"
 119:         mDataRow("Aggregate") = 2
 120:         mSourceTable.Rows.Add(mDataRow)
 121:  
 122:         mDataRow = mSourceTable.NewRow
 123:         mDataRow("Key") = "1/3/2008"
 124:         mDataRow("Variable") = "1/3/2008"
 125:         mDataRow("Aggregate") = 3
 126:         mSourceTable.Rows.Add(mDataRow)
 127:  
 128:  
 129:         Dim target As PivotTable = New PivotTable(mSourceTable, mKeyColumn, mVariableColumn, mAggregateColumn)
 130:  
 131:         Dim actual As DataTable
 132:         actual = target.Transform
 133:  
 134:         Assert.AreEqual(3, actual.Rows.Count, "Should have 3 rows")
 135:         Assert.AreEqual(4, actual.Columns.Count, "Should have 4 columns")
 136:         Assert.AreEqual(2, actual.Rows(1)(2), "Value in Column 1 2 should be ""2""")
 137:  
 138:         'Assert.Inconclusive("Verify the correctness of this test method.")
 139:  
 140:     End Sub
 141:  
 142:  
 143:     '''<summary>
 144:     ''' Test to see when a column is added that the correct ordinal is set.
 145:     ''' If Column 2 is added before 3 does it show as 2 or 3.
 146:     ''' Correct outcome should be 1,2,3
 147:     '''</summary>
 148:     <TestMethod()> _
 149:     Public Sub TransformTestColumnAddingOrder()
 150:         Dim mSourceTable As New DataTable("TestPivotTable")
 151:         Dim mKeyColumn As New DataColumn("Key", System.Type.GetType("System.DateTime"))
 152:         Dim mVariableColumn As New DataColumn("Variable", System.Type.GetType("System.String"))
 153:         Dim mAggregateColumn As New DataColumn("Aggregate", System.Type.GetType("System.Int32"))
 154:         Dim mDataRow As DataRow
 155:  
 156:         mSourceTable.Columns.Add(mKeyColumn)
 157:         mSourceTable.Columns.Add(mVariableColumn)
 158:         mSourceTable.Columns.Add(mAggregateColumn)
 159:  
 160:         ' Row #1
 161:         mDataRow = mSourceTable.NewRow
 162:         mDataRow("Key") = "1/1/2008"
 163:         mDataRow("Variable") = "Column1"
 164:         mDataRow("Aggregate") = 1
 165:         mSourceTable.Rows.Add(mDataRow)
 166:  
 167:         ' Row #2
 168:         mDataRow = mSourceTable.NewRow
 169:         mDataRow("Key") = "1/2/2008"
 170:         mDataRow("Variable") = "Column1"
 171:         mDataRow("Aggregate") = 1
 172:         mSourceTable.Rows.Add(mDataRow)
 173:  
 174:         mDataRow = mSourceTable.NewRow
 175:         mDataRow("Key") = "1/2/2008"
 176:         mDataRow("Variable") = "Column3"
 177:         mDataRow("Aggregate") = 2
 178:         mSourceTable.Rows.Add(mDataRow)
 179:  
 180:  
 181:         ' Row #3
 182:         mDataRow = mSourceTable.NewRow
 183:         mDataRow("Key") = "1/3/2008"
 184:         mDataRow("Variable") = "Column1"
 185:         mDataRow("Aggregate") = 1
 186:         mSourceTable.Rows.Add(mDataRow)
 187:  
 188:         mDataRow = mSourceTable.NewRow
 189:         mDataRow("Key") = "1/3/2008"
 190:         mDataRow("Variable") = "Column2"
 191:         mDataRow("Aggregate") = 2
 192:         mSourceTable.Rows.Add(mDataRow)
 193:  
 194:         mDataRow = mSourceTable.NewRow
 195:         mDataRow("Key") = "1/3/2008"
 196:         mDataRow("Variable") = "Column3"
 197:         mDataRow("Aggregate") = 3
 198:         mSourceTable.Rows.Add(mDataRow)
 199:  
 200:  
 201:         Dim target As PivotTable = New PivotTable(mSourceTable, mKeyColumn, mVariableColumn, mAggregateColumn)
 202:  
 203:         Dim actual As DataTable
 204:         actual = target.Transform
 205:  
 206:         Assert.AreEqual(3, actual.Rows.Count, "Should have 3 rows")
 207:         Assert.AreEqual(4, actual.Columns.Count, "Should have 4 columns")
 208:         Assert.AreEqual(2, actual.Rows(1)(3), "Value in Column 1 2 should be ""2""")
 209:         Assert.AreEqual("Column1", actual.Columns(1).ColumnName, "Column 1 should be named Column 1")
 210:         Assert.AreEqual("Column2", actual.Columns(2).ColumnName, "Column 2 should be named Column 2")
 211:         Assert.AreEqual("Column3", actual.Columns(3).ColumnName, "Column 3 should be named Column 3")
 212:     End Sub
 213: End Class
Digg It!DZone It!StumbleUponTechnoratiRedditDel.icio.usNewsVineFurlBlinkList

Currently rated 2.9 by 10 people

  • Currently 2.9/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Posted by Bryan on Wednesday, January 09, 2008 8:43 AM
Permalink | Comments (7) | Post RSSRSS comment feed

Comments

softrevi.com

Thursday, June 19, 2008 11:36 PM

pingback

Pingback from softrevi.com

Soft Review » Datatable Pivot/Crosstab

Melayu boleh us

Wednesday, May 06, 2009 10:16 PM

Melayu boleh

Thanks for the source code

sulumits retsambew us

Friday, May 08, 2009 8:34 AM

sulumits retsambew

very nice post, thanks.

Melayu Boleh us

Saturday, May 09, 2009 9:28 PM

Melayu Boleh

Thank for your source very useful

tukang nggame

Wednesday, May 13, 2009 8:15 PM

tukang nggame

Thanks for tutorial data table

Melayu Boleh sg

Wednesday, May 20, 2009 5:14 PM

Melayu Boleh

Thank You for your detail information..

Cheap Electric Guitar us

Wednesday, May 27, 2009 9:52 PM

Cheap Electric Guitar

Nice post. Hope to read more article from this great blog.