public static DataTable PivotData(DataTable _SourceTable, string RowField, string DataField, AggregateFunction Aggregate, params string[] ColumnFields)
{
DataTable dt = new DataTable();
string Separator = ".";
var RowList = (from x in _SourceTable.AsEnumerable()
select new { Name = x.Field <object>(RowField) })
.Distinct().OrderBy(x => x.Name);
var ColList = (from x in _SourceTable.AsEnumerable()
select new
{
Name = ColumnFields.Select(n => x.Field <object>(n))
.Aggregate((a, b) => a += Separator + b.ToString())
})
.Distinct()
.OrderBy(m => m.Name);
dt.Columns.Add(RowField);
foreach (var col in ColList)
{
dt.Columns.Add(string.IsNullOrEmpty(col.Name.ToString()) ? "[Empty]" : col.Name.ToString(), typeof(float));
}
foreach (var RowName in RowList)
{
DataRow row = dt.NewRow();
row[RowField] = RowName.Name.ToString();
foreach (var col in ColList)
{
string strFilter = RowField + " = '" + RowName.Name + "'";
string[] strColValues =
col.Name.ToString().Split(Separator.ToCharArray(),
StringSplitOptions.None);
for (int i = 0; i < ColumnFields.Length; i++)
{
strFilter += " and " + ColumnFields[i] +
" = '" + strColValues[i] + "'";
}
row[string.IsNullOrEmpty(col.Name.ToString()) ? "[Empty]" : col.Name.ToString()]
= GetData(_SourceTable, strFilter, DataField, Aggregate);
}
dt.Rows.Add(row);
}
return(dt);
}
// <summary>
/// Pivots the DataTable based on provided RowField, DataField, Aggregate Function and ColumnFields.//
/// </summary>
/// <param name="RowField">The column name of the Source Table which you want to spread into rows</param>
/// <param name="DataField">The column name of the Source Table which you want to spread into Data Part</param>
/// <param name="Aggregate">The Aggregate function which you want to apply in case matching data found more than once</param>
/// <param name="ColumnFields">The List of column names which you want to spread as columns</param>
/// <returns>A DataTable containing the Pivoted Data</returns>
public static DataTable Pivot(this DataTable dtable, string RowField, string DataField, AggregateFunction Aggregate, params string[] ColumnFields)
{
DataTable dt = new DataTable();
string Separator = ".";
var RowList = (from x in dtable.AsEnumerable() select new { Name = x.Field <object>(RowField) }).Distinct();
// Gets the list of columns .(dot) separated.
var ColList = (from x in dtable.AsEnumerable()
select new
{
Name = ColumnFields.Select(n => x.Field <object>(n))
.Aggregate((a, b) => a += Separator + b.ToString())
})
.Distinct()
.OrderBy(m => m.Name);
dt.Columns.Add(RowField);
foreach (var col in ColList)
{
dt.Columns.Add(col.Name.ToString()); // Cretes the result columns.//
}
foreach (var RowName in RowList)
{
DataRow row = dt.NewRow();
row[RowField] = RowName.Name.ToString();
foreach (var col in ColList)
{
string strFilter = RowField + " = '" + RowName.Name + "'";
string[] strColValues = col.Name.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None);
for (int i = 0; i < ColumnFields.Length; i++)
{
strFilter += " and " + ColumnFields[i] + " = '" + strColValues[i] + "'";
}
row[col.Name.ToString()] = dtable.GetData(strFilter, DataField, Aggregate);
}
dt.Rows.Add(row);
}
return(dt);
}
public override int GetHashCode()
{
var result = 37;
unchecked
{
result = result * 17 + base.GetHashCode();
result = result * 17 + SeqNumber;
result = result * 17 + (ColumnFields?.Select(f => f.GetHashCode()).UncheckedSum() ?? 0);
result = result * 17 + (int)FieldType;
result = result * 17 + FormatTypeId;
result = result * 17 + (CurrencyCode?.GetHashCode() ?? 0);
result = result * 17 + HasSubtotals.GetHashCode();
result = result * 17 + HasTotals.GetHashCode();
result = result * 17 + (int)TotalsAggregation;
result = result * 17 + IsVisible.GetHashCode();
if (ColumnGroupId != null)
{
result = result * 17 + ColumnGroupId.Value;
}
}
return(result);
}
