Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I am using C# winforms to create an application that needs to turn a datatable into a pivot table. I have the pivot table working fine from a SQL end, but creating it from a datatable seems trickier. I couldn't seem to find anything built into .NET for this.
NOTE: I do have to do this from a .NET side as I manipulate the data prior to creating the pivot.
I've read through some articles that did some similar things as this, but I've had difficultly applying them to my problem.
*I have a datatable with the columns "StartDateTime", "Tap", and "Data". The startdates should be grouped together and data values averaged (sometimes more than one data value per startdate). The table is shown below:
Pivot table should output like the image below (not rounded values though). The column numbers are the distinct tap numbers (one for each unique one).
How can I go about creating this pivot table from the datatable?
EDIT: forgot to mention, these tap values are not always from 1-4, they do vary in number and value.
–
var oDT = new DataTable();
var dfq = new Dictionary<DateTime, DataRow>;
oDT.Columns.Add("StartDateTime", typeof(DateTime));
for (int i = 0; i < inDT.Rows.Count; i++) {
var key = (DateTime)inDT.Rows[i][0];
var row = (String)inDT.Rows[i][2];
var data = (Double)inDT.Rows[i][1];
if (!oDT.Columns.Contains(row)) {
oDT.Columns.Add(row);
if (dfq.ContainsKey(key)) {
dfq[key][row] = data;
} else {
var oRow = oDT.NewRow();
oRow[0] = key;
oRow[row] = data;
dfq.Add(key, oRow);
oDT.Rows.Add(oRow);
// pivot table in oDT
–
Pivot table like that can be easily calculated with free
NReco.PivotData
aggregation library:
DataTable t; // assume it has: StartDateTime, Data, Tap
var pivotData = new PivotData(
new string[] {"StartDateTime","Tap"},
new AverageAggregatorFactory("Data"),
new DataTableReader(t) );
var pvtTbl = new PivotTable(
new [] {"StartDateTime"}, // row dimension(s)
new [] {"Tap"}, // column dimension(s),
pivotData);
Row and column keys are represented by pvtTbl.RowKeys and pvtTbl.ColumnKeys collections; values / totals could be accessed by indexer (for example: pvtTbl[0,0].Value ) or by row+column key (for example: pivotData[new Key(new DateTime(2012, 3, 30, 11, 42, 00)), new Key(4)].Value ).
–
Maybe this will help you. It is in vb.net.
Public Function pivot_datatable(ByVal datatable_source As DataTable, ByVal datacolumn_rows As DataColumn(), ByVal datacolumn_columns As DataColumn, ByVal datacolumn_value As DataColumn) As DataTable
Dim temp_datacolumn As DataColumn
Dim current_datarow As DataRow
Dim datarow_destination As DataRow = Nothing
Dim current_column_name As String = ""
Dim primary_key() As DataColumn = New DataColumn() {}
Dim key_columns() As Object
Dim newOrdinal As Integer
Dim i As Integer
Dim sort_string As String = ""
pivot_datatable = New DataTable()
For Each temp_datacolumn In datatable_source.Columns
If temp_datacolumn.Ordinal <> datacolumn_columns.Ordinal AndAlso temp_datacolumn.Ordinal <> datacolumn_value.Ordinal Then
array_insert(primary_key, pivot_datatable.Columns.Add(temp_datacolumn.ColumnName, temp_datacolumn.DataType))
sort_string &= temp_datacolumn.ColumnName & " ASC, "
End If
pivot_datatable.PrimaryKey = primary_key
For Each current_datarow In datatable_source.Rows ' Main Process to add values to pivot table
current_column_name = current_datarow(datacolumn_columns.Ordinal).ToString
If Not pivot_datatable.Columns.Contains(current_column_name) Then ' Column is new
temp_datacolumn = pivot_datatable.Columns.Add(current_column_name, datacolumn_value.DataType)
newOrdinal = temp_datacolumn.Ordinal
For i = newOrdinal - 1 To datatable_source.Columns.Count - 2 Step -1
If temp_datacolumn.ColumnName.CompareTo(pivot_datatable.Columns(i).ColumnName) < 0 Then
newOrdinal = i
End If
temp_datacolumn.SetOrdinal(newOrdinal)
End If
key_columns = New Object() {}
For Each data_column As DataColumn In datacolumn_rows
array_insert(key_columns, current_datarow(data_column.Ordinal).ToString)
Next data_column
datarow_destination = pivot_datatable.Rows.Find(key_columns)
If datarow_destination Is Nothing Then ' New Row
datarow_destination = pivot_datatable.NewRow()
For Each temp_datacolumn In datatable_source.Columns
If temp_datacolumn.Ordinal <> datacolumn_columns.Ordinal AndAlso temp_datacolumn.Ordinal <> datacolumn_value.Ordinal Then
datarow_destination(temp_datacolumn.ColumnName) = current_datarow(temp_datacolumn.ColumnName)
End If
pivot_datatable.Rows.Add(datarow_destination)
End If
datarow_destination(current_column_name) = current_datarow(datacolumn_value.Ordinal)
Return sort_datatable(pivot_datatable, sort_string.Substring(0, sort_string.Length - 2))
Catch ex As Exception
Return Nothing
End Try
End Function
Another small piece of code to pivot any table you would want:
var dataTable = new DataTable(); // your input DataTable here!
var pivotedDataTable = new DataTable(); //the pivoted result
var firstColumnName = "Year";
var pivotColumnName = "Codes";
pivotedDataTable.Columns.Add(firstColumnName);
pivotedDataTable.Columns.AddRange(
dataTable.Rows.Cast<DataRow>().Select(x => new DataColumn(x[pivotColumnName].ToString())).ToArray());
for (var index = 1; index < dataTable.Columns.Count; index++)
pivotedDataTable.Rows.Add(
new List<object> { dataTable.Columns[index].ColumnName }.Concat(
dataTable.Rows.Cast<DataRow>().Select(x => x[dataTable.Columns[index].ColumnName])).ToArray());
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
