Use of the SQL Server 2005 pivot operator

Original: Use of the SQL Server 2005 pivot operator

The pivot,unpivot operator is one of the new features supported by SQL Server 2005 and is primarily used to implement row-to-column conversions. This article mainly describes the operation of the pivot operator and how to implement the row and column transformation of dynamic pivot.

For Unpivot and column conversions under SQL Server 2000, please refer to my other articles.

I. The syntax of pivot

SELECT   [non-pivoted column],--optional   [additional non-pivoted columns],--optional   [first pivoted  Column],   [additional pivoted columns] from (   select query producing SQL data for pivot   --Select pivot columns As dimensions and   --value columns as measures from SQL tables) as Tablealias PIVOT (   <aggregation FUNCTION&G t; (column for aggregation or measure column)--MIN,MAX,SUM,ETC for   []   in (     [first pivoted column], ..., [LA St pivoted column]) as   Pivottablealias ORDER by clause–optional

Ii. examples of use of pivot

1. Use of static pivot
To demonstrate, extract some records from the Northwind database to generate a new orders table, and then use pivot to convert the rows to columns.

Use Tempdbgoselect Year (OrderDate) as [year], CustomerID, OD. Quantityinto dbo. Orders from NorthWind: Orders as O JOIN NorthWind: [Order Details] As od on o.orderid = od. Orderidwhere O.customerid in (' Bonap ', ' Bottm ', ' ANTON ') SELECT customerid,[1996],[1997],[1998]from dbo.     Orderspivot (SUM (Quantity) for [year] in ([1996],[1997],[1998])) x/* the structure of the pivot in TSQL: The source table used to generate the pivot data source as an input table Pivot Table aggregation columns and selection of pivot columns implementation of PIVOT in TSQL:1-> the Orders table in the previous example is equivalent to an input table.   Contains three columns of customerid,[year],quantity.   Year is a pivot column that is used to build the dimension. Pivot first groups the columns that are outside the aggregation column and aggregates them.       In this column, a column other than the quantity of the aggregate column is implemented first, that is, grouping the customerid,year and aggregating it quantity, equivalent to doing the following: */SELECT CustomerID, [year] , SUM (Quantity) as Totalfrom dbo. Ordersgroup by CustomerID, [Year]order to CustomerID/*result:customerid year Total--------------------      ------------ANTON 1996 24ANTON 1997 295ANTON 1998 40BONAP 1996 181BONAP  1997 486BONAP    1998 313BOTTM 1996 81BOTTM 1997 454BOTTM 1998 421*//*2->pivot according to [year   ] in clause, in the result set to establish the corresponding new column, in this case, the column, for the new column, in the value, take the intermediate result set corresponding to the value.   For the value in the Customer anton,1996 column, select the corresponding total value in the intermediate result, as in the same column.   and name the intermediate result pivot table x.  3-> the outermost SELECT statement generates the final result from the pivot table, where the Orders table has only columns, so the results are returned directly with a SELECT, with nested select references to the following example.         --Result: CustomerID 1996 1997 1998-------------------------------------------ANTON 24 295 40BONAP 181 486 313BOTTM 81 454 421*/The following is an example of more than one column for the input table, with data from SQL Serv The AdventureWorks of ER 2005 is implemented in the same principle. SELECT * FROM (select year (duedate) [year], Case MONTH (DueDate) when 1 and ' January ' when 2 Then ' February ' when 3 then ' March ' When 4 Then ' April ' if 5 then ' could ' when 6 then ' June ' when 7 then ' July ' when 8 then ' August ' when 9 then ' September ' W HEN ' October ' when one then ' November ' when the ' December ' END as [Month], ProductID, Orderqtyfrom producti Mnl   WorkOrder) WorkOrder PIVOT (SUM (OrderQty) for [Month] in ([January],[february],[march],[april],[may],[june],[july],[augus T],[september],[october],[november],[december]) Xorder by [year], ProductID--result: End part omitted/* Year Pro Ductid January February March April may June July August----------------           ----------------------------------------------------------------------------------------------2002 3          8480 16870 12960 9530 19390 14170 26200 35870 2002 316 1842 3704 2910 2252 4738 3496 7624 10778 2002 32        4 1842 3704 2910 2252 4738 3496 7546 10600 2002        327 921 1852 1455 1126 2369 1748 3773 5300 2002    328 414     1048 872 458 1272 992 1786 2632 * * 

2. Use of dynamic pivot

Use AdventureWorks; GO-the first way to generate a pivot column, using coalesce to join a string declare @PivotColHeader VARCHAR (MAX) SELECT @PivotColHeader = COALESCE (@PivotColHe Ader + ', [' + CAST (name as varchar) + '] ', ' [' + CAST (name as varchar) + '] ')---the Name in the example is converted to varchar or char type, note: In Cast and Co When using varchar in Nvert, the default value for display n is 30FROM sales.salesterritorygroup by name/*--the second way to generate a pivot column, using the For XML path method SELECT @ Pivotcolheader = STUFF ((SELECT DISTINCT ', [' + CAST (Name as varchar) + '] ' from Sales.sale Sterritory for XML PATH (")), */declare @PivotTableSQL NVARCHAR (MAX) SET @PivotTableSQL = N ' Sele CT * FROM (SELECT year (h.orderdate) [year],t.name,h.totaldue from Sales.SalesOrderHeader hleft JOIN Sale            S.salesterritory T on h.territoryid = T.territoryid) as PivotData PIVOT (SUM (TotalDue)                            For Name in (' + @PivotColHeader + ')) as X ' ExeCUTE sp_executesql @PivotTableSQL--result: Partial results omitted/*year Australia Canada Central France Germany northeast--------------------------------------------           ---------------------------------------------------------------------------------------------2001 1446497.1744 2173647.1453 1263884.1024 199531.723 262752.4184 754833.2045 20 02 2380484.8387 7215430.5017 3518185.4756 1717145.7439 575960.0974 32 75322.1694 2003 4547123.2777 8186021.9178 4015356.874 4366078.3475 27 14826.4297 3833030.25 2004 3823410.2386 3926712.8926 1771532.7396 285    3948.6596 2386224.5508 1406555.6861 * *

   Add a summary column to the dynamic pivot

DECLARE @PivotColHeader varchar (max) DECLARE @TotalCol varchar (max) SELECT @PivotColHeader =--    Use the COALESCE function to generate a column header coalesce (@PivotColHeader + ', [' + CAST (name as varchar) + '] ', ' [' + CAST (name as varchar) + '] ') , @TotalCol = COALESCE (@TotalCol + ', SUM ([' + CAST (name as varchar) + ']) as [' + CAST (name as varchar) + '] ', ' SUM ([' + CAST (name as varchar) + ']) as [' + CAST (name as varchar) + '] ')--use the COALESCE function to generate a summary string from Sales.salesterrit Orydeclare @PivotTableSQL NVARCHAR (MAX) SET @PivotTableSQL = N ' select * FROM (Select CAST (Year (h.orderdate) As CHAR (4)) [Year],t.name,h.totaldue from Sales.SalesOrderHeader hleft JOIN sales.salesterritory T on H.territory     ID = T.territoryid) as Pivotdatapivot (SUM (TotalDue) for Name in (' + @PivotColHeader + ')) as X UNION Select ' GrandTotal ', ' + @TotalCol + ' from (select CAST (Year (h.orderdate) as CHAR (4)) [Year],t.name,h.totalduefrom S Ales. SalesOrderHeader H   Left joins sales.salesterritory TON H.territoryid = T.territoryid) as Pivotdatapivot (SUM (TotalDue) for Name in (' + @Pivo Tcolheader + ') as Y '--print @PivotTableSQL EXECUTE sp_executesql @Pi                Vottablesql--result: Partial result omitted/*year Australia Canada Central France Germany northeast Northwest------------------------------------------------        -------------------------------------------------------------------------------------------------------------2001 1446497.1744 2173647.1453 1263884.1024 199531.723 262752.4184 75483 3.2045 2703481.7947 2002 2380484.8387 7215430.5017 3518185.4756 171714 5.7439 575960.0974 3275322.1694 5651688.6685 2003 4547123.2777 8186021 .9178 4015356.874          4366078.3475 2714826.4297 3833030.25 7494658.0357 2004 3823410.2386 3926712.8926 1771532.7396 2853948.6596 2386224.5508 1406555.6861 495 2772.2793 grandtotal 12197515.5294 21501812.4574 10568959.1916 9136704.474 5939     763.4963 9269741.31 20802600.7782 * *

Considerations for generating Summary columns;
1-> uses the COALESCE function to generate column headings.
2-> uses the COALESCE function to generate a string with the sum sum function and an alias specified.
3-> uses union to implement joins for two select. and convert [year] to a string, because year (h.orderdate) has a value of INT, and ' GrandTotal ' is a string, union or union all uses the number and type of columns that must be listed.

Use of the SQL Server 2005 pivot operator