Last week I needed a feature of the DataTable object that would reproduce the Transpose capability we can find in Excel (which transforms a horizontal array into its vertical equivalent). This means that we will be able to convert the rows of a DataTable into columns.

I was sure that feature exists but couldn’t find it. So I decided to write my own.

Downloadable demo

This month, the demo solution offers both a VB and a C# project. The solution was saved using Visual Studio 2010 but could be reused in any version of .Net (even version 1.0).

Creating the test UI

The UI of the demo application is really simple. It is composed of 2 buttons and 2 DataGridViews. The first button loads random data into the first DataGridView (see the Creating random data section). The second button transposes the first DataGridView content into the second one (see the Transposing the data section).

Figure 1: the demo application in action

Creating random data

Before being able to show data, we need to fill a DataTable. Instead of filling it from a database, I decided to create a method to new up a DataTable and to fill it with random values for 10 years for 5 products.

This is my method that fills a DataTable with random values for demoing purposes:

'Create a demo table with random values
Private Function CreateSourceDataTable() As DataTable
    'Create a table
    Dim dt As New DataTable("Source")
    With dt
        With .Columns
            .Add("Year", GetType(Integer))
            .Add("Product 1", GetType(String))
            .Add("Product 2", GetType(String))
            .Add("Product 3", GetType(String))
            .Add("Product 4", GetType(String))
            .Add("Product 5", GetType(String))
        End With

        'Add rows
        For i As Integer = 1 To 10
            .LoadDataRow(New Object() {2000 + i,
                                       mRandomClass.Next,
                                       mRandomClass.Next,
                                       mRandomClass.Next,
                                       mRandomClass.Next,
                                       mRandomClass.Next},
                                   True)
        Next
    End With

    Return dt
End Function

Once we have this method, you can easily display the content of the first DataTable into the first DataGridView with this single line of code:

DataGridView1.DataSource = CreateSourceDataTable()

If you run the demo application at this point, the top grid should be field with random values after you clicked the Load button.

Transposing the data

Then the fun part is coming.

We now need to take the content of the top DataGridView and convert each row into columns.

The first thing we need to do is to get back our DataTable. Because I didn’t saved it in a class level variable, I can retrieve it directly from the DataGridView DataSource property like this:

Dim dtSource As DataTable = CType(DataGridView1.DataSource, DataTable)

Then, I can create a new DataTable object that will receive the transposed content.

Dim dtnew As New DataTable

To this new DataTable, I will create a Pivot column (which will later be filled with the product name – the name of my actual columns) and also one new column for each row of my source (the years in my scenario):

dtnew.Columns.Add("Pivot")
For i As Integer = 0 To dtSource.Rows.Count - 1
    dtnew.Columns.Add(dtSource.Rows(i).Item(0).ToString)
Next

We are now ready for the main loop. The first iterator (the i loop) is in charge of looping through all the columns of the source DataTable (each column becoming a row). Using the NewRow method of the destination DataTable, we create a new empty row and fill the first column (called Pivot earlier) with the name of the column. The following loop (the j loop) loops through all the rows to copy the content of the source cell to its destination equivalent place. Finally, the newly created row is added to the destination DataTable. This complete paragraph only requires this little snippet of code:

For i As Integer = 1 To dtSource.Columns.Count - 1
    Dim dr As DataRow = dtnew.NewRow

    dr.Item(0) = dtSource.Columns(i).ColumnName
    For j As Integer = 0 To dtSource.Rows.Count - 1
        dr.Item(j + 1) = dtSource.Rows(j).Item(i).ToString
    Next

    dtnew.Rows.Add(dr)
Next

The final step is to show the result into the second DataGridView with this single line of code:

DataGridView2.DataSource = dtnew

Limitations

The code is working as expected when you have a limited number of rows and columns in your DataTable object. If you have thousands of rows/columns, you might want to revert back to another structure (list, array, …).

Extending the feature

Do you remember last month we have built extension methods? This Transpose method would be another great one to ad to these extensions if your version of Visual Studio supports it (meaning Visual Studio 2008 and above).

Conclusion

Another simple but useful feature to add to your toolbox for the day you will need it.