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 da
-- 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
代码
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)
