在本文中，我将介绍如何制作数据透视表。

数据透视表 PIVOT

PIVOT 和 UNPIVOT 是关系运算符，用于将表值表达式转换为另一个表。 PIVOT 通过将唯一值从输出中的一列转换为多列来旋转表值表达式，并在输出中需要的任何剩余列值上执行聚合函数。 简单来说，它只是将指定列的值转换为列名，从而有效地旋转表。 PIVOT 的逆操作是 UNPIVOT。 UNPIVOT 运算符将表值表达式的列旋转为列名。

语法

select <non-pivoted column>,
[first pivoted column] as <column name>,
[second pivoted column name] as <column name>,
. . . . . .
[last pivoted column] as <column name>
from
(select query that produces the data)
as TableAlias
PIVOT
(
<aggregation function>(column being aggregated)--MIN,MAX,SUM,etc
FOR
[<column that contains the values that will become column headers>]
IN ([first pivoted column], [second pivoted column].....[last pivoted column] )
AS PivotTableAlias
<optional ORDER BY clause>

SQL Server 2008 数据透视表示例

创建表

create table ProductSales(CustomerName varchar(15), ProductName varchar(15),QuantityNeeded int)

插入数据并查看表数据

insert into ProductSales

select 'Tim', 'Coke',5 union all

select 'Tim', 'Bread',3 union all

select 'John','Sandwich',4 union all

select 'Tim','Milk',2 union all

select'John','Bread',3 union all

select 'John','Coke',6 union all

select 'Mike','Soda',12 union all

select 'Mike', 'Coke',5 union all

select 'Mike', 'Bread',1 union all

select 'Mike','Sandwich',2 union all

select 'Tim','Milk',6

select * from ProductSales

数据透视表

SELECT ProductName, John, Mike

FROM (

SELECT CustomerName, ProductName, QuantityNeeded

FROM ProductSales) up

PIVOT (SUM(QuantityNeeded) FOR CustomerName IN (John, Mike)) AS [pivot]

ORDER BY ProductName

GO