Implementing row and column conversions of tables in SQL Server 2005

The pivot and Unpivot relational operators are the new features provided by SQL Server 2005, so when you use pivot and UNPIVOT for databases that are upgraded to SQL Server 2005, the compatibility level of the database must be set to 90 (you can use sp_ Dbcmptlevel stored procedures to set the compatibility level.

Using pivot and UNPIVOT in the FROM clause of a query, you can perform some action on an input table-valued expression to get another form of table. The pivot operator rotates the rows of the input table into columns and can perform aggregation operations on the rows at the same time. The UNPIVOT operator, however, performs the opposite of the pivot operator, which rotates the columns of the input table into rows.

The syntax format for using the pivot and Unpivot relational operators in the FROM clause is as follows:

　　[ FROM { ＜table_source＞ } [ ,...n ] ]
＜table_source＞ ::= {
　table_or_view_name [ [ AS ] table_alias ]
　＜pivoted_table＞ | ＜unpivoted_table＞
}
＜pivoted_table＞ ::=table_source PIVOT ＜pivot_clause＞ table_alias
＜pivot_clause＞ ::=( aggregate_function ( value_column )
　FOR pivot_column
　　IN ( ＜column_list＞ )
)
＜unpivoted_table＞ ::=table_source UNPIVOT ＜unpivot_clause＞ table_alias
＜unpivot_clause＞ ::=( value_column FOR pivot_column IN ( ＜column_list＞ ) )
＜column_list＞ ::= column_name [ , ... ] table_source PIVOT ＜pivot_clause＞

Specifies a perspective on the Pivot_column column in the Table_source table. Table_source can be a table, table expression, or subquery.

Aggregate_function

System or user-defined aggregate functions. Note: The Count (*) system aggregate function is not allowed.

Value_column

The value column that the pivot operator uses for the calculation. When used with Unpivot, Value_column cannot be the name of an existing column in the input table_source.

For Pivot_column

The pivot column for the pivot operator. Pivot_column must be a type that can be implicitly or explicitly converted to nvarchar ().

When using Unpivot, Pivot_column is the column name that extracts the output from Table_source, and the table_source cannot have existing columns of that name.

In (column_list)

In the pivot clause, column_list lists the values of the column names in Pivot_column that will be the output table.

In the UNPIVOT clause, column_list lists all the column names in Table_source that will be extracted to a single pivot_column.

Table_alias

The alias of the output table.

Unpivot-Unpivot_clause >

Specifies that the value of multiple columns specified by column_list in the input table be reduced to a single column named Pivot_column.

A common scenario where you might use pivot is that you need to generate a crosstab report to summarize the data. A crosstab is a more extensive table format, for example, the product sales table shown in Figure 5-4 is a typical crosstab, where the months and product categories can continue to be added. However, this format is not easy to manage when data tables are stored, and to store tabular data such as Figure 5-4, a datasheet usually needs to be designed as a structure like figure 5-5. This creates a problem where users want data to be easy to manage, and they want to be able to generate a tabular data that is easy to read. Fortunately, Pivot has facilitated this conversion.

Figure 5-4 Product Sales Chart 5-5 datasheet structure

Suppose the Sales.orders table contains ProductID (Product ID), OrderMonth (sales month), and subtotal (sales) columns, and stores what is shown in table 5-2.

Table 5-2 the contents of the Sales.orders table

ProductID	OrderMonth	SubTotal
1	5	100.00
1	6	100.00
2	5	200.00
2	6	200.00
2	7	300.00
3	5	400.00
3	5	400.00

Execute the following statement:

　　SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月
FROM
Sales.Orders PIVOT
(
SUM (Orders.SubTotal)
FOR Orders.OrderMonth IN
( [5], [6], [7] )
) AS pvt
ORDER BY ProductID;

In the above statement, Sales.orders is an input table, Orders.ordermonth is a pivot column (pivot_column), and Orders.subtotal is a value column (Value_column). The preceding statement obtains the output result set in the following steps:

A Pivot First group the input table sales.orders by columns other than the Value column (ProductID and OrderMonth), similar to executing the following statement:

　　SELECT ProductID,
OrderMonth,
SUM (Orders.SubTotal) AS SumSubTotal
FROM Sales.Orders
GROUP BY ProductID,OrderMonth;

This will get an intermediate result set, as shown in table 5-3. Only 3 of ProductID products were added together (a value of 800) as a result of 2 sales records in May.

Table 5-3 The results of the Sales.orders table after a grouped summary

ProductID	OrderMonth	Sumsubtotal
1	5	100.00
1	6	100.00
2	5	200.00
2	6	200.00
2	7	300.00
3	5	800.00

b Pivot according to the value of 5, specified in the for Orders.ordermonth in 6, 7, first in the result set to establish a column named 5, 6, 7, and then from the intermediate results shown in Figure 5-3, remove the OrderMonth column in the corresponding values, placed in 5, 6, 7 columns. The alias of the result set obtained at this time is PVT (see the designation of AS Pvt in the statement). The contents of the result set are shown in table 5-4.

Table 5-4 result sets obtained using for Orders.ordermonth in ([5], [6], [7])

productid	5 d>	6	7
1	100.00	null
200.00	200.00	200.00
3	800.00	null	null

C Finally, according to select ProductID, [5] as May, [6] as June, [7] as the designation of July from, the data is retrieved from the alias Pvt result set, and the columns named 5, 6, 7 are renamed in the final result set to May, June, July, respectively. What needs to be noted here is the meaning of the From, which represents retrieving data from the PVT result set derived from the pivot relational operator, rather than retrieving the data from the sales.orders. The resulting set of results is shown in table 5-5.

Table 5-5 The final result set by converting rows to columns from the Sales.orders table shown in table 5-2

May	June	July
1	100.00	null
200.00	200.00	200.00
3	800.00	null	null

Unpivot performs almost the opposite operation with pivot, converting columns to rows. However, Unpivot is not entirely pivot, because the data has been grouped together during the execution of the pivot, so using unpivot does not reproduce the results of the original table-valued expression. Assuming that the result set shown in table 5-5 is stored in a table named Mypvt, it is now necessary to convert the column identifiers "May", "June", and "July" to the row values corresponding to the corresponding product IDs (that is, the format shown in table 5-3). This means that two columns must be identified separately, one for storing the month, and one for storing sales. For ease of understanding, these two columns are still named OrderMonth and Sumsubtotal respectively. Refer to the following statement:

　　CREATE TABLE MyPvt (ProductID int, 五月int, 六月 int, 七月int); --建立MyPvt表
GO
　　--将表5-5中所示的值插入到MyPvt表中
INSERT INTO MyPvt VALUES (1,100,100,0);
INSERT INTO MyPvt VALUES (2,200,200,200);
INSERT INTO MyPvt VALUES (3,800,0,0);
　　--执行UNPIVOT
SELECT ProductID, OrderMonth, SubTotal
FROM
　MyPvt UNPIVOT
　(SubTotal FOR OrderMonth IN
　　(五月, 六月, 七月)
　)AS unpvt;

The preceding statement obtains the output result set in the following steps:

A First, the structure of a temporary result set is established, which contains columns in the MYPVT table other than in (May, June, July), and the value columns (SubTotal) and Pivot columns (ORDERMONTH) specified in the SubTotal for OrderMonth.

b The data is retrieved line-by-row in mypvt, and the table's column name (specified in in (May, June, July) is placed in the OrderMonth column, and the corresponding value is placed in the Subtotal column. The final result set is shown in table 5-6.

Table 5-6 result sets obtained using Unpivot

ProductID	OrderMonth	SubTotal
1	May	100
1	June	100
1	July	0
2	May	200
2	June	200
2	July	200
3	May	800
3	June	0
3	July	0