publicstatic DataTable Pivot(IDataReader dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn) { DataTable tmp =new DataTable(); DataRow r; int i, pValIndex, pNameIndex; string s; // Add non-pivot columns to the data table: pValIndex = dataValues.GetOrdinal(pivotValueColumn); pNameIndex = dataValues.GetOrdinal(pivotNameColumn); // Loop through columns for (i =0; i <= dataValues.FieldCount -1; i++) { if (i != pValIndex && i != pNameIndex) { DataColumn dc =new DataColumn(dataValues.GetName (i), dataValues.GetFieldType(i)); tmp.Columns.Add(dc); // Add key column if (dc.ColumnName == keyColumn) tmp.PrimaryKey =new DataColumn[] { dc }; } } // now, fill up the table with the data while (dataValues.Read()) { // assign the pivot values to the proper column; add new columns if needed: s = dataValues[pNameIndex].ToString(); if (!tmp.Columns.Contains(s)) tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex)); // Create new row after adding any additional columns r = tmp.NewRow(); // Add pivot value r[s] = dataValues[pValIndex]; // Add all non-pivot column values to the new row: for (i =0; i <= dataValues.FieldCount -3; i++) r[i] = dataValues[tmp.Columns[i].ColumnName]; // Look for key DataRow rowFound = tmp.Rows.Find(r[keyColumn]); // Add new record if not found if (null== rowFound) { tmp.Rows.Add(r); } else// Key already exists .. just update it { rowFound[s] = dataValues[pValIndex]; } } // Close the DataReader dataValues.Close(); // and that's it! return tmp; }
dataValues -- this is any open DataReader object, ready to be transformed and pivoted into a DataTable. As mentioned, it should be fully grouped, aggregated, sorted and ready to go. 要处理的DataReader
keyColumn -- This is the column in the DataReader which serves to identify each row. In the previous example, this would be CustomerID. Your DataReader's recordset should be grouped and sorted by this column as well. X轴字段
pivotNameColumn -- This is the column in the DataReader that contains the values you'd like to transform from rows into columns. In the example, this would be ProductName. Y轴字段
pivotValueColumn -- This is the column that in the DataReader that contains the values to pivot into the appropriate columns. For our example, it would be Qty, which has been defined in the SELECT statement as SUM(Qty).数据区字段
例子:
privatevoid Sample() { // call this from a form . SqlConnection conn; SqlCommand com; DataGrid dg =new DataGrid(); dg.Parent =this; dg.Dock = DockStyle.Fill; String SQL = "select o.customerID, c.CompanyName, p.productName, sum(od.quantity) as Qty "+ " from orders o "+ " inner join [order details] od on o.orderID = od.orderID "+ " inner join Products p on od.ProductID = p.ProductID "+ " inner join Customers c on o.CustomerID = c.CustomerID "+ " group by o.customerID, c.CompanyName, p.ProductName "+ " order by o.customerID "; conn =new SqlConnection( "Server=(local);Database=Northwind;uid=xx;pwd=xx"); conn.Open(); com =new SqlCommand(SQL, conn); try { dg.DataSource = Pivot(com.ExecuteReader(),"CustomerID", "ProductName","Qty"); } catch (SqlException ex) { MessageBox.Show(ex.Message); } }