How to create pivot table in C# and display it in DataGrid?
NOTE: it is available
version 2.0 of PivotTable
It exists
well-know solution for pre-sorted and pre-grouped values.
What if we would like to pivot free set? Sample below will pivot any DataTable and produce new summarized DataTable. You can pivot Array or ArrayList also, first you should convert your Array to DataTable and then call Pivot method. Data is ready to display in grid.
public DataTable Pivot(
DataTable src,
string VerticalColumnName,
string HorizontalColumnName,
string ValueColumnName )
{
DataTable dst = new DataTable();
if (src == null || src.Rows.Count == 0)
return dst;
// find all distinct names for column and row
ArrayList ColumnValues = new ArrayList();
ArrayList RowValues = new ArrayList();
foreach (DataRow dr in src.Rows)
{
// find all column values
object column = dr[VerticalColumnName];
if (!ColumnValues.Contains(column))
ColumnValues.Add(column);
//find all row values
object row = dr[HorizontalColumnName];
if (!RowValues.Contains(row))
RowValues.Add(row);
}
ColumnValues.Sort();
RowValues.Sort();
//create columns
dst = new DataTable();
dst.Columns.Add(VerticalColumnName, src.Columns[VerticalColumnName].DataType);
Type t = src.Columns[ValueColumnName].DataType;
foreach (object ColumnNameInRow in RowValues) {
dst.Columns.Add(ColumnNameInRow.ToString(), t);
}
//create destination rows
foreach (object RowName in ColumnValues) {
DataRow NewRow = dst.NewRow();
NewRow[VerticalColumnName] = RowName.ToString();
dst.Rows.Add(NewRow);
}
//fill out pivot table
foreach (DataRow drSource in src.Rows) {
object key = drSource[VerticalColumnName];
string ColumnNameInRow = Convert.ToString(drSource[HorizontalColumnName]);
int index = ColumnValues.IndexOf(key);
dst.Rows[index][ColumnNameInRow] = sum(dst.Rows[index][ColumnNameInRow], drSource[ValueColumnName]);
}
return dst;
}
dynamic sum(dynamic a, dynamic b) {
if (a is DBNull && b is DBNull)
return DBNull.Value;
else if (a is DBNull && !(b is DBNull))
return b;
else if (!(a is DBNull) && b is DBNull)
return a;
else
return a + b;
}
Proof:
object DBNULL = DBNull.Value;
DataTable src = new DataTable();
DataTable dst = null;
src.Columns.Add("City", typeof(string));
src.Columns.Add("Product", typeof(string));
src.Columns.Add("Value", typeof(float));
src.Rows.Add("City A", "Product 1", 1.2f);
src.Rows.Add("City C", "Product 2", 0.3f);
src.Rows.Add("City A", "Product 1", 1.0f);
src.Rows.Add("City B", "Product 3", 2.2f);
src.Rows.Add("City B", "Product 1", 1.5f);
src.Rows.Add("City A", "Product 2", 0.8f);
src.Rows.Add("City A", "Product 4", 1.1f);
PivotTable test = new PivotTable();
dst = test.Pivot(src, "City", "Product", "Value");
CollectionAssert.AreEqual(new object[] { "City A", 2.2f, 0.8f, DBNULL, 1.1f }, dst.Rows[0].ItemArray);
CollectionAssert.AreEqual(new object[] { "City B", 1.5f, DBNULL, 2.2f, DBNULL }, dst.Rows[1].ItemArray);
CollectionAssert.AreEqual(new object[] { "City C", DBNULL, 0.3f, DBNULL, DBNULL }, dst.Rows[2].ItemArray);
Enjoy!
