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 in dt.Rows)
                Console.WriteLine("{0}\t\t{1}\t{2}\t{3}", row["EmployeeID"],
                    row["Y2002"], row["Y2003"], row["Y2004"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

The output is shown in Figure 4-17.

Figure 4-17. Output for RetrievePivotTable solution

The second solution unpivots the result set in the first solution to recreate the original result set.

The C# code in Program.cs in the project RetrieveUnpivotTable is shown in Example 4-18.

Example 4-18. File: Program.cs for RetrieveUnpivotTable solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace RetrieveUnpivotTable
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT EmployeeID, OrderYear, TotalDue FROM " +
                "(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) pvtTable " +
                "UNPIVOT " +
                "(TotalDue FOR OrderYear IN (Y2002, Y2003, Y2004)) unpvt " +
                "ORDER BY EmployeeID, OrderYear";

            // Fill the DataTable
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.Fill(dt);

            Console.WriteLine("EmployeeID\tOrderYear\tTotalDue");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("{0}\t\t{1}\t\t{2}",
                    row["EmployeeID"], row["OrderYear"], row["TotalDue"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

The output is shown in Figure 4-18.

Figure 4-18. Output for RetrieveUnpivotTable solution

Discussion

The PIVOT and UNPIVOT operators introduced in SQL Server 2005 manipulate a tablevalued expression into another table. These operators are essentially opposites of each other—PIVOT takes rows and puts them into columns, whereas UNPIVOT takes columns and puts them into rows.

PIVOT

The PIVOT operator rotates unique values in one column into multiple columns in a result set. The syntax of the PIVOT operator is:

	<pivoted_table> ::=
	    table_source PIVOT <pivot_clause> table_alias
	<pivot_clause> ::=
	    ( aggregate_function ( value_column )
	        FOR pivot_column
	        IN ( <column_list>)
	    )
	<column_list> ::=
	    column_name [, ...]

Where:

table_source

The table, view, or derived table to use in the T-SQL statement.

table_alias

An alias for table_source—this is required for PIVOT operators.

aggregate_function

A system- or user-defined aggregate function. COUNT(*) is not allowed.

value_column

The column containing the pivoted value.

pivot_column

The column containing the values into which the value_column aggregate values are grouped. These values are the pivot columns.

<column_list>

The pivot column names of the output table.

UNPIVOT

UNPIVOT does the opposite of PIVOT, rotating multiple column values into rows in a result set. The only difference is that NULL column values do not create rows in the UNPIVOT result set. The syntax of the UNPIVOT operator is:

	<unpivoted_table> ::=
	    table_source UNPIVOT <unpivot_clause> table_alias
	<unpivot_clause> ::=
	    ( value_column FOR pivot_column IN ( <column_list> ) )
	<column_list> ::=
	    column_name [, ...]

The arguments are the same as those for the PIVOT operator.