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.
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?
ReplyDeleteNice 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.
Deletethanks you so mutch !
ReplyDeleteFor many people this is the best solution here see how to do it. website
ReplyDeleteVery good topic, similar texts are I do not know if they are as good as your work out. check this out
ReplyDeleteThis is our job to deliver the best application. gb whatsapp download.
ReplyDeleteHi 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