It exists well-know solution for pre-sorted and pre-grouped values.
What if we would like to pivot free set? Below you will find my solution.
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!
Posts
