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.
ReplyDeleteMuy clara esta solución para pivotar un DataTable en .NET, me ha servido para un informe en VB.NET. Después de programar, me gusta seguir la liga en futbol libre tv hd liga online para ver los partidos en directo. ¡Gracias por compartir el código!
ReplyDelete