C# 依据DataTable创建透视表

/// <summary>

/// 依据原始数据创建透视表

/// 透视表:CreatePivotTable(scoreTable, "Course", "Score","No=学号;Name=姓名;Sex=性别", "No,Name,Sex");

/// </summary>

/// <paramname="dataSource">原始数据</param>

/// <paramname="totalColumn">distinct统计字段</param>

/// <paramname="valueColumn">数据字段</param>

/// <paramname="nullValue">不存在时的填充值</param>

/// <paramname="aliasColumns">生成的透视表列别名 eg: f1=栏位1;f2=栏位2</param>

/// <paramname="groupFields">分组字段 eg:f1,f2</param>

/// <returns></returns>

private DataTableCreatePivotTable(DataTable dataSource,string totalColumn,string valueColumn, string aliasColumns,string groupFields,object nullValue=null)

{

    if (dataSource ==null ||string.IsNullOrEmpty(totalColumn) ||string.IsNullOrEmpty(valueColumn) ||string.IsNullOrEmpty(groupFields))returnnull;

    int i;

    string[] arry, arry2;

    DataTable result, table;

    DataView dataView;

    DataRow newRow;

    DataColumn col;

    List<DataColumn> columns;

    Type valueType;

    StringBuilder wheresb;

    if (nullValue ==null)nullValue =DBNull.Value;

    string[] groupColumns = groupFields.Split(',');

    result = new DataTable();

    //构建数据列

    columns = new List<DataColumn>();

    for (i =0;i < groupColumns.Length;i++)

    {

        col = new DataColumn(groupColumns[i],dataSource.Columns[groupColumns[i]].DataType);

        result.Columns.Add(col);

        columns.Add(col);

    }

    if (columns.Count >0)result.PrimaryKey =columns.ToArray();

    dataView = dataSource.DefaultView;

    dataView.RowFilter ="";

    table = dataView.ToTable(true,totalColumn);

    valueType = dataSource.Columns[valueColumn].DataType;

    columns.Clear();

    foreach (DataRow rowintable.Rows)

    {

        col = new DataColumn(row[0].ToString(),valueType);

        result.Columns.Add(col);

        columns.Add(col);

    }

    //填充分组栏位数据

    table = dataView.ToTable(true,groupColumns);

    wheresb = new StringBuilder();

    foreach (DataRow rowintable.Rows)

    {

        newRow = result.NewRow();

        wheresb.Clear();

        for (i =0;i < groupColumns.Length;i++)

        {

            newRow[groupColumns[i]] =row[groupColumns[i]];

            wheresb.AppendFormat(" and {0}='{1}'",groupColumns[i],row[groupColumns[i]].ToString());

        }

        //查找数据填充剩余栏位的数据

        for (i =0;i < columns.Count;i++)

        {

            dataView.RowFilter =string.Format("{0}='{1}' {2}",totalColumn,columns[i].ColumnName,wheresb.ToString());

            newRow[columns[i]] =dataView.Count >0 ? dataView[0][valueColumn] :nullValue;

        }

        result.Rows.Add(newRow);

    }

    dataView.RowFilter ="";

    //设置透视表列别名

    if (!string.IsNullOrEmpty(aliasColumns))

    {

        arry = aliasColumns.Split(';');

        for (i =0;i < arry.Length;i++)

        {

            arry[i] =arry[i].Trim();

            if (arry[i].Length <1)continue;

            arry2 = arry[i].Split('=');

            if (arry2.Length <2)continue;

            arry2[0] =arry2[0].Trim();

            arry2[1] =arry2[1].Trim();

            if (arry2[0].Length <1 ||arry2[1].Length <1)continue;

            if (!result.Columns.Contains(arry2[0]))continue;

            result.Columns[arry2[0]].ColumnName =arry2[1];

        }

    }

    return result;

}