ADO.NET 3.5 Cookbook, 2nd Edition

4.15. Retrieving a Pivot and Unpivot Table

Problem

You need to take rows in a result set and put (pivot) them into columns or take columns in a result set and put (unpivot) them into rows.

Solution

Use the T-SQL PIVOT and UNPIVOT operators.

Two solutions are presented. The first solution sums the total orders by employee in the Purchasing.PurchaseOrderHeader table in AdventureWorks for the years 2002, 2003, and 2004; pivots the total amount by year; and sorts the result set by employee ID.

The C# code in Program.cs in the project RetrievePivotTable is shown in Example 4-17.

Example 4-17. File:Program.cs for RetrievePivotTable solution

using System; using System.Data; using System.Data.SqlClient; namespace RetrievePivotTable { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT EmployeeID, [2002] Y2002, " + "[2003] Y2003, [2004] Y2004 FROM " + "(SELECT YEAR(OrderDate) OrderYear, EmployeeID, TotalDue " + "FROM Purchasing.PurchaseOrderHeader) poh " + "PIVOT (SUM(TotalDue) FOR OrderYear IN " + "([2002], [2003], [2004])) pvt " + "WHERE EmployeeID BETWEEN 200 AND 300 " + "ORDER BY EmployeeID"; // Fill the DataTable DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.Fill(dt); Console.WriteLine("Employee ID\t2002\t\t2003\t\t2004"); Console.WriteLine("-----------\t----\t\t----\t\t----"); foreach (DataRow row ...