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?
ReplyDeletethanks you so mutch !
ReplyDeleteFor many people this is the best solution here see how to do it. website
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