T-SQL Pivot Tables(行列转换) in SQL Server 2005/2008

T-SQL Pivot Syntax

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 [<column name containing values for pivot table columns>]

  IN (

    [first pivoted column], ..., [last pivoted column]

  )

) AS PivotTableAlias

ORDER BY clause -- optional

T-SQL Pivot Table Examples in AdventureWorks SQL Server sample database

select

  PS.Name, P.Color, PIn.Quantity

from Production.Product P

inner join Production.ProductSubcategory PS

  on PS.ProductSubcategoryID = P.ProductSubcategoryID

left join Production.ProductInventory PIn

  on P.ProductID = PIn.ProductID

 

 

 

代码

  1  use  AdventureWorks
  2  go
  3  -- -geovindu@163.com  涂聚文
  4  select
  5     *
  6  from
  7  (
  8     select
  9      PS.Name, P.Color, PIn.Quantity
 10     from  Production.Product P
 11     inner   join  Production.ProductSubcategory PS
 12       on  PS.ProductSubcategoryID  =  P.ProductSubcategoryID
 13     left   join  Production.ProductInventory PIn
 14       on  P.ProductID  =  PIn.ProductID
 15  ) DataTable
 16  PIVOT
 17  (
 18  -- ISNULL(NULLIF(d,0)
 19     SUM (Quantity))
 20     FOR  Color
 21     IN  (
 22       [ Black ] , [ Blue ] , [ Grey ] , [ Multi ] , [ Red ] ,
 23       [ Silver ] , [ Silver/Black ] , [ White ] , [ Yellow ]
 24    )
 25  ) PivotTable
 26 
 27  --
 28  SELECT   *
 29  FROM  (
 30     SELECT
 31       YEAR (OrderDate)  [ Year ] ,
 32       MONTH (OrderDate)  [ Month ] ,
 33      SubTotal
 34     FROM  Sales.SalesOrderHeader
 35  ) TableDate
 36  PIVOT (
 37     SUM (SubTotal)
 38     FOR   [ Month ]   IN  (
 39       [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] , [ 5 ] , [ 6 ] , [ 7 ] , [ 8 ] , [ 9 ] , [ 10 ] , [ 11 ] , [ 12 ]
 40    )
 41  ) PivotTable
 42 
 43  --
 44  SELECT   *
 45  FROM  (
 46     SELECT
 47       YEAR (OrderDate)  [ Year ] ,
 48       CASE   MONTH (OrderDate)
 49         WHEN   1   THEN   ' January '
 50         WHEN   2   THEN   ' February '
 51         WHEN   3   THEN   ' March '
 52         WHEN   4   THEN   ' April '
 53         WHEN   5   THEN   ' May '
 54         WHEN   6   THEN   ' June '
 55         WHEN   7   THEN   ' July '
 56         WHEN   8   THEN   ' August '
 57         WHEN   8   THEN   ' September '
 58         WHEN   8   THEN   ' October '
 59         WHEN   8   THEN   ' November '
 60         WHEN   8   THEN   ' December '
 61       END   as   [ Month ] ,
 62      SubTotal
 63     FROM  Sales.SalesOrderHeader
 64  ) TableDate
 65  PIVOT (
 66     SUM (SubTotal)
 67     FOR   [ Month ]   IN  (
 68       [ January ] , [ February ] , [ March ] , [ April ] ,
 69       [ May ] , [ June ] , [ July ] , [ August ] ,
 70       [ September ] , [ October ] , [ November ] , [ December ]
 71    )
 72  ) PivotTable
 73 
 74  -- -
 75 
 76  SELECT   *
 77  FROM (
 78     SELECT
 79       YEAR (DueDate)  [ Year ] ,
 80       CASE   MONTH (DueDate)
 81         WHEN   1   THEN   ' January '
 82         WHEN   2   THEN   ' February '
 83         WHEN   3   THEN   ' March '
 84         WHEN   4   THEN   ' April '
 85         WHEN   5   THEN   ' May '
 86         WHEN   6   THEN   ' June '
 87         WHEN   7   THEN   ' July '
 88         WHEN   8   THEN   ' August '
 89         WHEN   9   THEN   ' September '
 90         WHEN   10   THEN   ' October '
 91         WHEN   11   THEN   ' November '
 92         WHEN   12   THEN   ' December '
 93       END   as   [ Month ] ,
 94      ProductID,
 95      OrderQty
 96     FROM  Production.WorkOrder
 97  ) WorkOrders
 98  PIVOT
 99  (
100     SUM (OrderQty)
101     FOR   [ Month ]   IN  (
102       [ January ] , [ February ] , [ March ] , [ April ] ,
103       [ May ] , [ June ] , [ July ] , [ August ] ,
104       [ September ] , [ October ] , [ November ] , [ December ]
105    )
106  )  AS  PivotTable
107  ORDER   BY   [ Year ] , ProductID
108 
109  -- -
110  DECLARE   @PivotColumnHeaders   VARCHAR ( MAX )
111  SELECT   @PivotColumnHeaders   =
112     COALESCE (
113       @PivotColumnHeaders   +   ' ,[ '   +   cast (Name  as   varchar )  +   ' ] ' ,
114       ' [ '   +   cast (Name  as   varchar ) +   ' ] '
115    )
116  FROM  Sales.SalesTerritory
117 
118  DECLARE   @PivotTableSQL   NVARCHAR ( MAX )
119  SET   @PivotTableSQL   =  N '
120    SELECT *
121    FROM (
122      SELECT
123        YEAR(H.OrderDate) [Year],
124        T.Name,
125        H.TotalDue
126      FROM Sales.SalesOrderHeader H
127      LEFT JOIN Sales.SalesTerritory T
128        ON H.TerritoryID = T.TerritoryID
129    ) AS PivotData
130    PIVOT (
131      SUM(TotalDue)
132      FOR Name IN (
133         '   +   @PivotColumnHeaders   +   '
134      )
135    ) AS PivotTable
136  '
137 
138  EXECUTE ( @PivotTableSQL )