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