/// <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); }