使用PIVOT把单列值转化为多列和聚合数据

PIVOT运算符能让我们创建交叉的查询，他把值转化为多列，使用聚合来根据新列对数据进行分组。

语法：

FROM table_source  PIVOT (aggregate_function(value_column)  FOR pivot_column in (<column_list>)) table_alias

下表描述PIVOT的参数：

参数	描述
table_source	要旋转数据的表
aggregate_function(value_column)	要在某列上使用的集合函数
pivot_column	用于创建列头的列
column_list	要从旋转列中旋转的值
table_alias	旋转结果集的表别名

 

 

 

 

 

未选装前的数据，查询结果显示了雇员的轮换和他们所在的部门：

SELECT s.Name ShiftName,h.BusinessEntityID,d.Name DepartmentName

FROM HumanResources.EmployeeDepartmentHistory h

INNER JOIN 　HumanResource.Department d on h.DepartmentID=d.DepartmentID

INNER JOIN　HumanResource.Shift s on h.ShiftID=s.ShiftID

WHERE 　EndDate IS NULL AND d.Name IN ('Production','Engineering','Marketing')

ORDER BY ShiftName

注意到，在同一个列中列出各种部门：

---

ShiftName　　　　　　　BusinessEntityID　　　　　　　　DepartmentName

Day　　　　　　　　　　3　　　　　　　　　　　　　　　　Engineering

Day　　　　　　　　　　9　　　　　　　　　　　　　　　　Engineering

...

Day　　　　　　　　　　2　　　　　　　　　　　　　　　　Marketing

Day　　　　　　　　　　6　　　　　　　　　　　　　　　　Marketing

...

Evening　　　　　　　　25　　　　　　　　　　　　　　　Production

Evening　　　　　　　　18　　　　　　　　　　　　　　　Production

Night　　　　　　　　　14　　　　　　　　　　　　　　　Production

Night　　　　　　　　　27　　　　　　　　　　　　　　　Production

...

Night　　　　　　　　　252　　　　　　　　　　　　　　Production　

(194行受影响)

---

 

 下面的查询把轮换的雇员数量和部门制旋转到列中：

SELECT ShiftName,Production,Engineering,Marketing

FROM (

　　SELECT s.Name ShiftName,h.BusinessEntityID,d.Name DepartmentName

　　FROM HumanResources.EmployeeDepartmentHistory h

　　INNER JOIN 　HumanResource.Department d on h.DepartmentID=d.DepartmentID

　　INNER JOIN　HumanResource.Shift s on h.ShiftID=s.ShiftID

　　WHERE 　EndDate IS NULL AND d.Name IN ('Production','Engineering','Marketing'))  AS a

PIVOT(

　　COUNT(BusinessEntityID)

　　FOR DepartmentName  IN ([Production],[Engineering],[Marketing])

) AS b

ORDER BY ShiftName

这个查询返回：

---

ShiftName　　　　　　　　Production　　　　　　　　Engineering　　　　　　　　Marketing

Day　　　　　　　　　　　 79　　　　　　　　　　　　　　6　　　　　　　　　　　　　9

Evening　　　　　　　　　　54　　　　　　　　　　　　　　0　　　　　　　　　　　　　0

Night　　　　　　　　　　　46　　　　　　　　　　　　　　0　　　　　　　　　　　　　0

(3行受影响)

---

在本例中：PIVOT(COUNT(BusinessEntityID)) 使用聚合函数COUNT()计算了雇员的数量。

FOR语句决定了哪些行值需要被转化成列，和普通的IN子句不同的是，在字符串周围不用单引号，而是使用方括号。

转载于:https://www.cnblogs.com/jacksong/archive/2012/07/18/2597168.html