asp.net对关系数据表查询并输出交叉表

本文介绍一下如何对关系数据表查询并输出交叉表，关于这方面的内容网上资料不少，但基本思路雷同，通过拼sql，用case。。when生成表头。邹建有一个很好的过程，输入简单的参数就可以转换和汇总求和。但是存储过程方式受具体数据库影响，不易扩展。所以想有个方法对内存表进行处理，生成交叉表。网上找了很久，终于发现了老外的一个方法，不敢独享，共同学习。(求和有问题，修改了一下，可以多加一列”合计”)

/// <summary>
        /// 行列转换数据表
        /// </summary>
        /// <param name="table">要转换的源表</param>
        /// <param name="columnX">显示在行的字段，类似mdx中rows</param>
        /// <param name="columnY">显示在列的字段，类似mdx中columns</param>
        /// <param name="columnZ">指标名</param>
        /// <param name="nullValue">空值表示方式</param>
        /// <param name="sumxValues">是否取x轴合计</param>

        /// <param name="sumyValues">是否取y轴合计</param> 
        /// <returns></returns>
       

public DataTable GetInversedDataTable(DataTable table, string columnX, string columnY, string columnZ, string nullValue, bool XsumValues,bool YsumValues)
        {
            //Create a DataTable to Return
            DataTable returnTable = new DataTable();

            if (string.IsNullOrEmpty(columnX))
            {
                columnX = table.Columns[0].ColumnName;
            }

            //Add a Column at the beginning of the table
            returnTable.Columns.Add(columnY);
            //Read all DISTINCT values from columnX Column in the provided DataTale
            List<string> columnXValues = new List<string>();

            foreach (DataRow dr in table.Rows)
            {

                string columnXTemp = dr[columnX].ToString();
                if (!columnXValues.Contains(columnXTemp))
                {
                    //Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.
                    columnXValues.Add(columnXTemp);
                    returnTable.Columns.Add(columnXTemp);
                }
            }
        //如果有合计列则增加合计列
            if (XsumValues)
            {
                columnXValues.Add("合计");
                returnTable.Columns.Add("合计",Type.GetType("System.Decimal"));
            }
            //Verify if Y and Z Axis columns re provided
            if (!string.IsNullOrEmpty(columnY) && !string.IsNullOrEmpty(columnZ))
            {
                //Read DISTINCT Values for Y Axis Column
                List<string> columnYValues = new List<string>();

                foreach (DataRow dr in table.Rows)
                {
                    if (!columnYValues.Contains(dr[columnY].ToString()))
                    {
                        columnYValues.Add(dr[columnY].ToString());
                    }
                }
                //Loop all Column Y Distinct Value
                foreach (string columnYValue in columnYValues)
                {
                    decimal sumx=decimal.Zero;
                    //Creates a new Row
                    DataRow drReturn = returnTable.NewRow();
                    drReturn[0] = columnYValue;
                    //foreach column Y value, The rows are selected distincted
                    DataRow[] rows = table.Select((columnY + "='") + columnYValue + "'");

                    //Read each row to fill the DataTable
                    foreach (DataRow dr in rows)
                    {
                        string rowColumnTitle = dr[columnX].ToString();

                        //Read each column to fill the DataTable
                        foreach (DataColumn dc in returnTable.Columns)
                        {
                            if (dc.ColumnName == rowColumnTitle)
                            {
                                //If Sum of Values is True it try to perform a Sum
                                //If sum is not possible due to value types, the value displayed is the last one read
                                    drReturn[rowColumnTitle] = dr[columnZ];
                                sumx+=decimal.Parse(dr[columnZ].ToString());
                            }
                        }
                    }
                    if(XsumValues)
                    {
                        drReturn["合计"] = sumx;
                    }
                    returnTable.Rows.Add(drReturn);
                }

            }
            else
            {
                throw new Exception("The columns to perform inversion are not provided");
            }
        //如果行总计则做汇总计算
            if (YsumValues)
            {
                DataRow dr=returnTable.NewRow();
                dr[0] = "总计";
                for (int i = 1; i < returnTable.Columns.Count; i++)
                {
                    for (int j = 0; j < returnTable.Rows.Count; j++)
                    {
                        decimal result1=decimal.Zero;
                        decimal.TryParse(dr[i].ToString(), out result1);
                        decimal result2=decimal.Zero;
                        if (decimal.TryParse(returnTable.Rows[j][i].ToString(), out result2))
                            dr[i] = (result1 + result2).ToString();
                    }
                }
                returnTable.Rows.Add(dr);
            }
            //if a nullValue is provided, fill the datable with it
            if (!string.IsNullOrEmpty(nullValue))
            {
                foreach (DataRow dr in returnTable.Rows)
                {
                    foreach (DataColumn dc in returnTable.Columns)
                    {
                        if (string.IsNullOrEmpty(dr[dc.ColumnName].ToString()))
                        {
                            dr[dc.ColumnName] = nullValue;
                        }
                    }
                }
            }

            return returnTable;
        }