#pragma warning disable CA1822 // Mark members as static
public DataTable Pivot(object dataSource, string[] primaryColumns, string pivotColumn, string pivotValueColumn,
#pragma warning restore CA1822 // Mark members as static
PivotAggregateFunction AggregateFunction = PivotAggregateFunction.First, PivotOptions options = null)
{
options ??= new PivotOptions();
if (primaryColumns == null || primaryColumns.Length <= 0)
{
throw new ArgumentNullException(nameof(primaryColumns));
}
if (string.IsNullOrWhiteSpace(pivotColumn))
{
throw new ArgumentNullException(nameof(pivotColumn));
}
if (string.IsNullOrWhiteSpace(pivotValueColumn))
{
throw new ArgumentException(nameof(pivotValueColumn));
}
using var reader = ScriptHostObject.GetDataSourceReader(dataSource,
new DataSourceParameters()
{
IgnoreErrors = options.IgnoreErrors, Columns = options.SelectColumns, SkipColumns = options.SkipColumns
});
if (reader == null)
{
throw new Exception("Input data are not provided.");
}
using var sourceTable = new DataTable("Source");
sourceTable.Load(reader);
var primColumns = primaryColumns.Select(pc => sourceTable.Columns[pc]).ToArray();
var pivColumn = sourceTable.Columns[pivotColumn];
var pivValueColumn = sourceTable.Columns[pivotValueColumn];
if (primColumns.Length <= 0 || primColumns.Contains(null))
{
throw new ArgumentException("Cannot find one or more of primary columns.");
}
if (pivColumn == null)
{
throw new ArgumentException($"Cannot find column '{pivotColumn}'.");
}
if (pivValueColumn == null)
{
throw new ArgumentException($"Cannot find column '{pivotValueColumn}'.");
}
var result = PivotToTable(sourceTable, primColumns, pivColumn, pivValueColumn, AggregateFunction);
return(result);
}
private static DataTable PivotToTable(DataTable dt, DataColumn[] primaryColumns,
DataColumn pivotColumn, DataColumn pivotValue, PivotAggregateFunction aggregateFunction)
{
string pivotColumnName = pivotColumn.ColumnName;
string pivotValueName = pivotValue.ColumnName;
string[] pkColumnNames = primaryColumns
.Select(c => c.ColumnName)
.ToArray();
var result = dt.DefaultView.ToTable("Pivot", true, pkColumnNames).Copy();
result.PrimaryKey = result.Columns.Cast <DataColumn>().ToArray();
dt.AsEnumerable()
.Select(row => Convert.ToString(row[pivotColumnName]))
.Where(str => !string.IsNullOrWhiteSpace(str))
.Distinct()
.OrderBy(str => str, StringLogicalComparer.DefaultComparer)
.ToList()
.ForEach(c => result.Columns.Add(c, pivotValue.DataType));
var dictValues = new Dictionary <(DataRow Row, string ColumnName), List <object> >();
foreach (DataRow row in dt.Rows)
{
var pivotColumnValue = row[pivotValueName];
if (pivotColumnValue == null || pivotColumnValue == DBNull.Value)
{
continue;
}
// find row to update
var aggRow = result.Rows.Find(
pkColumnNames
.Select(c => row[c])
.ToArray());
var columnName = Convert.ToString(row[pivotColumnName]);
if (dictValues.ContainsKey((aggRow, columnName)))
{
dictValues[(aggRow, columnName)].Add(pivotColumnValue);
}
