How to Pivot a DataTable in .NET

So I would have thought this problem would have already been solved by the Internets at large. As it turns out, I couldn’t find a very simple method to solve this relatively simple task. So here’s my attempt

Here’s a dead simple pivot table.

Let’s say I have a table that looks like this:

Person Age Sport
Susan 22 Tennis
Bob 29 Soccer
Terry 16 Basketball

And I want to pivot it to to look like this:

Person Susan Bob Terry
Age 22 29 16
Sport Tennis Soccer Basketball

Here’s How

Private Function PivotTable(oldTable As DataTable, 
                            Optional pivotColumnOrdinal As Integer = 0
                           ) As DataTable
    Dim newTable As New DataTable
    Dim dr As DataRow

    ' add pivot column name
    newTable.Columns.Add(oldTable.Columns(pivotColumnOrdinal).ColumnName)

    ' add pivot column values in each row as column headers to new Table
    For Each row In oldTable.Rows
        newTable.Columns.Add(row(pivotColumnOrdinal))
    Next

    ' loop through columns
    For col = 0 To oldTable.Columns.Count - 1
        'pivot column doen't get it's own row (it is already a header)
        If col = pivotColumnOrdinal Then Continue For

        ' each column becomes a new row
        dr = newTable.NewRow()

        ' add the Column Name in the first Column
        dr(0) = oldTable.Columns(col).ColumnName

        ' add data from every row to the pivoted row
        For row = 0 To oldTable.Rows.Count - 1
            dr(row + 1) = oldTable.Rows(row)(col)
        Next

        'add the DataRow to the new table
        newTable.Rows.Add(dr)
    Next

    Return newTable
End Function

Then just call like this:

Dim newTable = PivotTable(oldTable, 0)

And that’s that.

7 comments:

  1. Excellent Pivot Table, but how would you cope with multiple instances of the person, e.g. where there is a Surname field as well as a Forename in the table?

    ReplyDelete
    Replies
    1. Nice article. All the sections of your site covered with great functions. I really like the content and discussions on your site. Thanks a lot for allowing me to join. I love to see more about GB WhatsApp. Keep sharing and updating. Also share more posts with us. Thank you.

      Delete
  2. For many people this is the best solution here see how to do it. website

    ReplyDelete
  3. Very good topic, similar texts are I do not know if they are as good as your work out. check this out

    ReplyDelete
  4. This is our job to deliver the best application. gb whatsapp download.

    ReplyDelete
  5. Hi there! Forget the stress of submitting a substandard essay when https://essaysrescue.com/essaypro-review/ experts are ready to tackle your assignment with professionalism. Formatting, referencing, quotations, research citations, scholarly examples all are accommodated according to your requirements.

    ReplyDelete