C#实现Pivot（Cross Table）的一段代码和例子

public static 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).数据区字段

例子：

private void 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);

             }

        } 
 

转载自：http://weblogs.sqlteam.com/jeffs/archive/2005/05/11/5101.aspx