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>(column for aggregation or measure column) -- MIN,MAX,SUM,etc 

  FOR [] 
  IN ( 
    [first pivoted column], ..., [last pivoted column] 
  ) 
) AS PivotTableAlias 
ORDER BY clause – optional

USE tempdb
GO
SELECT YEAR(OrderDate) AS [Year]
       ,CustomerID 
       ,od.Quantity
INTO dbo.Orders       
FROM NorthWind..Orders AS o
    JOIN NorthWind..[Order Details] AS od
        ON o.OrderID = od.OrderID
WHERE o.CustomerID IN ('BONAP','BOTTM','ANTON')
SELECT CustomerID
    ,[1996],[1997],[1998]
FROM dbo.Orders
PIVOT (
       SUM(Quantity)
       FOR [Year] IN ([1996],[1997],[1998])
   )x
/*               
TSQL中pivot的结构：
    ●  用于生成pivot数据源的源表，作为一个输入表
    ●  pivot表
    ●  聚合列及透视列的选择

TSQL中pivot的实现：
1->上例中Orders表相当于是一个输入表。包含了CustomerID,[Year],Quantity 三个列。
   Year是透视列，用于生成维度。
   pivot首先将聚合列之外的列进行分组，并对其实现聚合。本列中则是对聚合列Quantity之外的列先实现分组，
   即对CustomerID，Year进行分组，并对其Quantity实现聚合，相当于先做如下处理：
*/                 
SELECT CustomerID
       ,[Year]
       ,SUM(Quantity) AS Total
FROM dbo.Orders
GROUP BY CustomerID
       ,[Year]
ORDER BY CustomerID               

/*    Result:           
CustomerID Year        Total
---------- ----------- -----------
ANTON      1996        24
ANTON      1997        295
ANTON      1998        40
BONAP      1996        181
BONAP      1997        486
BONAP      1998        313
BOTTM      1996        81
BOTTM      1997        454
BOTTM      1998        421
*/
/*
2->pivot根据FOR [Year] IN子句中的值，在结果集中来建立对应的新列，本例中即是列，，
   对于新列，，中的取值，取中间结果集中与之相对应的值。
   如对于客户ANTON，1996列中的值就选择中间结果中对应的Total值，同理列中为。
   并将中间结果pivot表命名为x。
   
3->最外层的SELECT语句从pivot表生成最终结果,此处因Orders表仅有列，故直接将结果用一个SELECT返回，有嵌套的SELECT参照下例。
  
--结果:  
CustomerID 1996        1997        1998
---------- ----------- ----------- -----------
ANTON      24          295         40
BONAP      181         486         313
BOTTM      81          454         421
*/         以下是为输入表多于一列的例子,数据来源于SQL server 2005的AdventureWorks，其实现的原理同上。
SELECT *  
FROM(
    SELECT YEAR(DueDate) [Year]
           ,CASE MONTH(DueDate)
            WHEN 1 THEN 'January' 
            WHEN 2 THEN 'February'
            WHEN 3 THEN 'March'
            WHEN 4 THEN 'April'
            WHEN 5 THEN 'May'
            WHEN 6 THEN 'June'
            WHEN 7 THEN 'July'
            WHEN 8 THEN 'August'
            WHEN 9 THEN 'September'
            WHEN 10 THEN 'October'
            WHEN 11 THEN 'November'
            WHEN 12 THEN 'December'
           END as [Month]
           ,ProductID
           ,OrderQty
    FROM Production.WorkOrder
)WorkOrder
    PIVOT (
           SUM(OrderQty)
           FOR [Month] IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
          )x
ORDER BY [Year], ProductID  
--Result: 末尾部分省略
/*  
Year        ProductID   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        324         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         
*/