SQL server 2005 UNPIVOT运算符的使用
UNPIVOT运算符相对于PIVOT运算符，它执行与PIVOT相反的操作，即将列转换到行。需要注意的是UNPIVOT运算符并不完全是PIVOT的逆向操作。因为执行PIVOT将会执行一次聚合，从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 无法重现原始表值表达式的结果，因为行已经被合并了。另外，UNPIVOT 的输入中的空值不会显示在输出中，而在执行 PIVOT 操作之前，输入中可能有原始的空值。
关于PIVOT运算符的使用，请参考：SQL server 2005 PIVOT运算符的使用。
一、UNPIVOT的语法
SELECT
[DataType]
, [DataValue]
, [UnpivotedCol1]
, [UnpivotedCol2]
, [UnpivotedCol3]
FROM table
UNPIVOT
(
[DataValue] FOR [DataType]
IN ([UnpivotedCol1],[UnpivotedCol1],[UnpivotedCol3],etc..)
)
AS Alias
二、UNPIVOT的使用例子
我们使用了SQL server 2005 PIVOT运算符的使用文章中的例子。如下所示，从NorthWind数据库生成数据源及PIVOT表。
1.静态UNPIVOT的用法
USE tempdb
GO
SELECT YEAR(OrderDate) AS [Year] --从NorthWind提取演示示例所需数据
,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')
AND od.Quantity > 20;
SELECT * FROM dbo.Orders;
/*以下列出部分结果集，年的数据省略
Year CustomerID Quantity
----------- ---------- --------
1996 BONAP 40
1996 BONAP 50
1996 BONAP 24
1996 ANTON 24
1996 BOTTM 30
1997 BOTTM 49
1997 BOTTM 25
1997 BOTTM 40
1997 BOTTM 50
1997 BOTTM 50
1997 BOTTM 30
*/
--1->.生成PIVOT数据
SELECT CustomerID
,[1996],[1997],[1998]
INTO #pivot_result
FROM dbo.Orders
PIVOT (
SUM(Quantity)
FOR [Year] IN ([1996],[1997],[1998])
)x
/* Result:
CustomerID 1996 1997 1998
---------- ----------- ----------- -----------
ANTON 24 205 NULL
BONAP 114 356 122
BOTTM 30 389 292
*/
--2->.生成UNPIVOT数据,实现列到行的转换
SELECT CustomerID
,Year
,Quantity
FROM #pivot_result
UNPIVOT
(
Quantity FOR Year IN ([1996],[1997],[1998])
) x
/* Result:
CustomerID Year Quantity
---------- ----------- -----------
ANTON 1996 24
ANTON 1997 205
BONAP 1996 114
BONAP 1997 356
BONAP 1998 122
BOTTM 1996 30
BOTTM 1997 389
BOTTM 1998 292
*/
--从上面的结果中我们看出CustomerID为ANTON用户在年，Quantity的值为NULL，即没有订单，使用UNPIOVT后忽略掉了NULL值。
--注意UNPIVOT后并没有回到PIVOT之前的数据，因为实现PIVOT后数据已经被汇总。
--3->.在UNPIVOT后的结果中重现NULL值
SELECT CustomerID
,Year
,CASE WHEN Quantity = 0 THEN NULL ELSE Quantity END AS Quantity --还原为NULL的值
FROM (
SELECT CustomerID,Year,Quantity
FROM (
SELECT CustomerID,
ISNULL([1996],0) AS [1996] --对年份列增加了一个ISNULL的判断
,ISNULL([1997],0) AS [1997]
,ISNULL([1998],0) AS [1998]
FROM #pivot_result) AS pivot_result
UNPIVOT
(
Quantity FOR Year IN ([1996],[1997],[1998])
) x
)y
/*
CustomerID Year Quantity
---------- ------- -----------
ANTON 1996 24
ANTON 1997 205
ANTON 1998 NULL --NULL值重现
BONAP 1996 114
BONAP 1997 356
BONAP 1998 122
BOTTM 1996 30
BOTTM 1997 389
BOTTM 1998 292
*/2.动态UNPIVOT的用法
USE tempd;
GO
DECLARE @UnpivotColHeader NVARCHAR(MAX)
,@UnpivotTableSQL NVARCHAR(MAX);
SELECT @UnpivotColHeader = --将列标题转换为行值
STUFF(
(SELECT N','+ QUOTENAME(x) AS [text()]
FROM (
SELECT COLUMN_NAME AS x
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
AND TABLE_NAME = N'pivot_result'
AND COLUMN_NAME NOT IN(N'CustomerID')
) AS x
ORDER BY x
FOR XML PATH('')),
1,1,N'');
SET @UnpivotTableSQL =
'SELECT CustomerID
,Year
,CASE WHEN Quantity = 0 THEN NULL ELSE Quantity END AS Quantity
FROM (
SELECT CustomerID,Year,Quantity
FROM (
SELECT CustomerID,
ISNULL([1996],0) AS [1996]
,ISNULL([1997],0) AS [1997]
,ISNULL([1998],0) AS [1998]
FROM pivot_result) AS pivot_result
UNPIVOT
(
Quantity FOR Year IN (' + @UnpivotColHeader + ')
) x
)y' + N';';
EXEC sp_executesql @UnpivotTableSQL;
三、总结
1.UNPIVOT操作符的第一个输入是保存被旋转属性值(Quantity)的目标列名称。
2.FOR关键字，指定保存被旋转列名称(Year)的目标列名称。
3.在IN子句的圆括号，指定要旋转的源列名称([1996],[1997],[1998])。
PIVOT运算符的使用，请参阅：http://blog.csdn.net/robinson_0612/archive/2010/03/16/5385117.aspx
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2010年03月23日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
