SQL Server 2005 PIVOT运算符的使用(一列多行数据合并为一行)

SQL Server 2005 PIVOT运算符的使用
PIVOT，UNPIVOT运算符是SQL Server 2005支持的新功能之一，主要用来实现行到列的转换。

本文主要介绍PIVOT运算符的操作，以及如何实现动态PIVOT的行列转换。

关于UNPIVOT及SQL Server 2000下的行列转换请参照本人的其它文章。

一、使用PIVOT和UNPIVOT命令的SQL Server版本要求
1、数据库的最低版本要求为SQL Server 2005 或更高。

2、必须将数据库的兼容级别设置为90 或更高。

3、查看我的数据库版本及兼容级别。

如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management St udio新建一个查询窗口输入：print @@version，运行之后在我的本机上得到：Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)
Apr 22 2011 19:23:43
Copyright (c) Microsoft Corporation
Data Center Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: S ervice Pack 1)
我们选择一个数据库，然后【右键】--【属性】，选择【选项】得到下图的信息。

在确认数据库的版本和兼容级别符合1、2点的要求后，才可以接着继续往下学习。

二、PIVOT的语法
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 PivotT ableAlias
ORDER BY clause–optional
三、PIVOT的使用例子
1、静态PIVOT的用法
为演示，从NorthWind数据库中提取一些记录生成新的Orders表，然后使用PIVO T将行转换到列。

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])
)AS x
/*
TSQL中pivot的结构：
● 用于生成pivot数据源的源表，作为一个输入表
● pivot表
● 聚合列及透视列的选择
TSQL中pivot的实现：
1->上例中Orders表相当于是一个输入表。

包含了CustomerID,[Year],Quantity 三个列。

Year是透视列，用于生成维度。

pivot首先将聚合列之外的列进行分组，并对其实现聚合。

本列中则是对聚合列Quant ity之外的列先实现分组，
即对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子句中的值，在结果集中来建立对应的新列，本例中即是列[1996],[1997],[1998]
对于新列[1996],[1997],[1998]中的取值，取中间结果集中与之相对应的值。

如对于客户ANTON，列中的值就选择中间结果中对应的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 Server2005的AdventureWork s，其实现的原理同上。

SELECT*
FROM
(
SELECT
YEAR(DueDate)[Year],
CASE MONTH(DueDate)
WHEN1 THEN'January'
WHEN2 THEN'February'
WHEN3 THEN'March'
WHEN4 THEN'April'
WHEN5 THEN'May'
WHEN6 THEN'June'
WHEN7 THEN'July'
WHEN8 THEN'August'
WHEN9 THEN'September'
WHEN10 THEN'October'
WHEN11 THEN'November'
WHEN12 THEN'December'
END AS[Month],
ProductID,
OrderQty
FROM Production.WorkOrder
)AS WorkOrder
PIVOT
(
SUM(OrderQty)
FOR[Month]IN([January],[February],[March],[April],[May],[June],[Jul y],[August],[September],[October],[November],[December])
)AS x
ORDER BY[Year],ProductID
--Result: 末尾部分省略
Year ProductID January February March April Ma y 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 1 272 992 1786 2632
*/
2、动态PIVOT的使用
USE AdventureWorks;
GO
--第一种生成透视列的方法，使用了COALESCE来联接字符串
DECLARE@PivotColHeader VARCHAR(MAX)
SELECT@PivotColHeader=
COALESCE(@PivotColHeader+',['+cast(Name as varchar)+']',
'['+cast(Name as varchar)+']')--示例中Name转换为varchar或char 类型，注意：在CAST 和CONVERT 中使用varchar 时，显示n的默认值为
FROM Sales.SalesTerritory
GROUP BY Name
/*
--第二种生成透视列的方法，使用了FOR XML PATH方法
SELECT @PivotColHeader =
STUFF(
(
SELECT DISTINCT ',[' + cast(Name as varchar) + ']'
FROM Sales.SalesTerritory
FOR XML PATH('')
),
1,1,'')
*/
DECLARE@PivotT ableSQL NVARCHAR(MAX)
SET@PivotTableSQL=N'
SELECT *
FROM
(
SELECT
YEAR(H.OrderDate) [Year],
,
H.TotalDue
FROM Sales.SalesOrderHeader H
LEFT JOIN Sales.SalesTerritory T ON H.TerritoryID = T.TerritoryID ) AS PivotData
PIVOT
(
SUM(TotalDue)
FOR Name IN
(
'+@PivotColHeader+'
)
) AS x '
EXECUTE sp_executesql@PivotT ableSQL
--Result:部分结果省略
/*
Year Australia Canada Central
France Germany Northeast
----------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
2001 1446497.1744 2173647.1453 1263884.1024 199531.723 262752.4184 754833.2045
2002 2380484.8387 7215430.5017 3518185.4756 1717145.7439 575960.0974 *******.1694
2003 4547123.2777 8186021.9178 4015356.874 4366078.3475 2714826.4297 3833030.25
2004 3823410.2386 3926712.8926 1771532.7396 2853948.6596 2386224.5508 1406555.6861
*/
3、对该动态pivot增加汇总列
DECLARE@PivotColHeader VARCHAR(MAX)
DECLARE@TotalCol VARCHAR(MAX)
SELECT@PivotColHeader=--使用COALESCE函
数生成列标题
COALESCE(@PivotColHeader+',['+cast(Name as varchar)+']',
'['+cast(Name as varchar)+']')
,
@TotalCol=COALESCE(@TotalCol+', SUM(['+cast(Name as varchar) +']) AS ['+cast(Name as varchar)+']'
,'SUM(['+cast(Name as varchar)+']) AS ['+cast(Name as varchar) +']')--使用COALESCE函数生成汇总字符串
FROM Sales.SalesTerritory
DECLARE@PivotT ableSQL NVARCHAR(MAX)
SET@PivotTableSQL=N'
SELECT *
FROM
(
SELECT
CAST(YEAR(H.OrderDate) AS CHAR(4)) [Year],
,
H.TotalDue
FROM Sales.SalesOrderHeader H
LEFT JOIN Sales.SalesTerritory T ON H.TerritoryID = T.TerritoryID ) AS PivotData
PIVOT
(
SUM(TotalDue)
FOR Name IN
(
'+@PivotColHeader+'
)
) AS x
UNION
SELECT ''GrandTotal'', '+@TotalCol+'
FROM
(
SELECT
CAST(YEAR(H.OrderDate) AS CHAR(4)) [Year],
,
H.TotalDue
FROM Sales.SalesOrderHeader H
LEFT JOIN Sales.SalesTerritory T ON H.TerritoryID = T.TerritoryID ) AS PivotData
PIVOT
(
SUM(TotalDue)
FOR Name IN
(
'+@PivotColHeader+'
)
) AS y '
--PRINT @PivotTableSQL
EXECUTE sp_executesql@PivotT ableSQL
--Result:部分结果省略
/*
Year Australia Canada Central
France Germany Northeast Northwe st
---------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
2001 1446497.1744 2173647.1453 1263884.1024 199531.723 262752.4184 754833.2045 2703481.7947
2002 2380484.8387 7215430.5017 3518185.4756 1717145.7439 575960.0974 *******.1694 5651688.6685
2003 4547123.2777 8186021.9178 4015356.874 4366078.3475 2714826.4297 3833030.25 7494658.0357
2004 3823410.2386 3926712.8926 1771532.7396 2853948.6596 2386224.5508 1406555.6861 4952772.2793
GrandTotal 12197515.5294 21501812.4574 10568959.1916 9136704.474 5939763.4963 9269741.31 20802600.7782
*/
生成汇总列的注意事项：
1）使用COALESCE函数生成列标题。

2）使用COALESCE函数生成带有SUM求和函数并且指定了别名的字符串。

3）使用UNION对两个SELECT来实现联接。

且将[Year]转换为字符串，因为YEAR (H.OrderDate)得值为INT ，而''GrandTotal''为字符串，UNION 或UNION ALL使用时必须列的数量和类型相对应。