/// <summary>
/// Checks whether specified columns do exists within given data.
/// </summary>
/// <param name="data">Data to search in.</param>
/// <param name="pivotInfo">Pivot information with column specification.</param>
private static void validate(DataView data, PivotInfo pivotInfo)
{
const string errorFormat = "The column {0} does not exists in given data.";
foreach (var col in pivotInfo.RowFields.Union(pivotInfo.ColumnFields).Union(pivotInfo.DataFields))
{
if (!data.Table.Columns.Contains(col))
{
throw new Exception(String.Format(errorFormat, col));
}
}
}
/// <summary>
/// Creates a new data table with pivoted data.
/// </summary>
/// <remarks>It is like a group by over row and column fields.</remarks>
/// <param name="data">Data view containing data to pivot.</param>
/// <param name="pivotInfo">Information specifying the pivotation.</param>
/// <returns>A data table with pivoted data.</returns>
private DataTable pivotDataFlat(DataView data, PivotInfo pivotInfo)
{
var dt = new DataTable();
var fields = pivotInfo.RowFields.Union(pivotInfo.ColumnFields).ToArray();
var dataFieldColumnName = pivotInfo.DataFieldColumnName;
//Creates the columns
foreach (var field in fields)
{
dt.Columns.Add(field);
}
if (pivotInfo.DataFields.Any())
{
dt.Columns.Add(dataFieldColumnName, typeof(string));
}
foreach (var func in pivotInfo.Aggregates)
{
dt.Columns.Add(func.Name, func.DataType);
}
var distinctFieldValues = getDistinctFieldsValues(data, fields);
//Create the rows
foreach (var values in distinctFieldValues)
{
var rowFilter = getFilterForFields(fields, values, String.Empty);
if (pivotInfo.DataFields.Any())
{
foreach (var dataField in pivotInfo.DataFields)
{
var row = dt.NewRow();
fillFields(fields, row, values);
row[dataFieldColumnName] = dataField;
foreach (var func in pivotInfo.Aggregates)
{
row[func.Name] = getData(data, rowFilter, dataField, func);
}
dt.Rows.Add(row);
}
}
else
{
var row = dt.NewRow();
fillFields(fields, row, values);
dt.Rows.Add(row);
}
}
return(dt);
}
private static string getNameForColumnFields(PivotInfo pivotInfo, DataRow row)
{
var columnNames = new List <string>();
foreach (var fieldName in pivotInfo.ColumnFields)
{
string value = row[fieldName].ToString();
if (!string.IsNullOrEmpty(value))
{
columnNames.Add(value);
}
}
return(columnNames.ToString(pivotInfo.ColumnFieldsSeparator.ToString()));
}
/// <summary>
/// Creates a new data table containing the pivoted data.
/// </summary>
/// <param name="data">Data view containing data to pivot.</param>
/// <param name="pivotInfo">Information specifying the pivotation.</param>
/// <returns>Pivoted data.</returns>
public DataTable PivotData(DataView data, PivotInfo pivotInfo)
{
validate(data, pivotInfo);
switch (pivotInfo.Mode)
{
case PivotInfo.PivotMode.CrossTable:
return(pivotDataCross(data, pivotInfo));
case PivotInfo.PivotMode.FlatTable:
return(pivotDataFlat(data, pivotInfo));
default:
throw new InvalidEnumArgumentException();
}
}
/// <summary>
/// Creates a new data table containing the pivoted data.
/// </summary>
/// <remarks>It creates a group by analysis as cross table (row fields vs. column fields).</remarks>
/// <param name="data">Data view containing data to pivot.</param>
/// <param name="pivotInfo">Information specifying the pivotation.</param>
/// <returns>Pivoted data.</returns>
private DataTable pivotDataCross(DataView data, PivotInfo pivotInfo)
{
var dt = new DataTable();
//Creates the row columns
foreach (var field in pivotInfo.RowFields)
{
dt.Columns.Add(field);
}
var distinctColumnFieldsValues = getDistinctValuesFor(data, pivotInfo.ColumnFields);
if (pivotInfo.ColumnFields.Any())
{
//Creates the result columns
foreach (var columnValue in distinctColumnFieldsValues)
{
var name = getNameForColumnFields(pivotInfo, columnValue);
foreach (var func in pivotInfo.Aggregates)
{
if (pivotInfo.DataFields.Count > 1)
{
foreach (var datafield in pivotInfo.DataFields)
{
dt.Columns.Add(getColumnNameFor(name, func, datafield, pivotInfo.ColumnFieldsSeparator), func.DataType);
}
}
else
{
dt.Columns.Add(getColumnNameFor(name, func, pivotInfo.ColumnFieldsSeparator), func.DataType);
}
}
if (!pivotInfo.Aggregates.Any())
{
dt.Columns.Add(name);
}
}
}
else
{
foreach (var func in pivotInfo.Aggregates)
{
if (pivotInfo.DataFields.Count > 1)
{
foreach (var datafield in pivotInfo.DataFields)
{
dt.Columns.Add(getColumnNameFor(func, datafield), func.DataType);
}
}
else
{
dt.Columns.Add(func.Name, func.DataType);
}
}
}
// Gets the list of row headers
var distinctRowFieldsValues = getDistinctFieldsValues(data, pivotInfo.RowFields);
//Create the rows
foreach (var rowFieldsValues in distinctRowFieldsValues)
{
var row = dt.NewRow();
var rowFilter = getFilterForFields(pivotInfo.RowFields, rowFieldsValues, String.Empty);
fillFields(pivotInfo.RowFields, row, rowFieldsValues);
if (pivotInfo.ColumnFields.Any())
{
// Build filter for column fields
foreach (var columnFieldsValues in distinctColumnFieldsValues)
{
var filter = getFilterForFields(pivotInfo.ColumnFields, columnFieldsValues, rowFilter);
var name = getNameForColumnFields(pivotInfo, columnFieldsValues);
// Get aggregated value of data field
foreach (var func in pivotInfo.Aggregates)
{
if (pivotInfo.DataFields.Count > 1)
{
foreach (var datafield in pivotInfo.DataFields)
{
row[getColumnNameFor(name, func, datafield, pivotInfo.ColumnFieldsSeparator)] = getData(data, filter, datafield, func);
}
}
else if (pivotInfo.DataFields.Count == 1)
{
row[getColumnNameFor(name, func, pivotInfo.ColumnFieldsSeparator)] = getData(data, filter, pivotInfo.DataFields[0], func);
}
else
{
row[getColumnNameFor(name, func, pivotInfo.ColumnFieldsSeparator)] = DBNull.Value;
}
}
if (!pivotInfo.Aggregates.Any())
{
row[name] = DBNull.Value;
}
}
}
else
{
foreach (var func in pivotInfo.Aggregates)
{
if (pivotInfo.DataFields.Count > 1)
{
foreach (var datafield in pivotInfo.DataFields)
{
row[getColumnNameFor(func, datafield)] = getData(data, rowFilter, datafield, func);
}
}
else if (pivotInfo.DataFields.Count == 1)
{
row[func.Name] = getData(data, rowFilter, pivotInfo.DataFields[0], func);
}
else
{
row[func.Name] = DBNull.Value;
}
}
}
dt.Rows.Add(row);
}
return(dt);
}