SQL server 2005 UNPIVOT运算符的使用_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1846 | 回复: 0   主题: SQL server 2005 UNPIVOT运算符的使用        下一篇 
shunli
注册用户
等级:新兵
经验:36
发帖:59
精华:0
注册:2011-9-23
状态:离线
发送短消息息给shunli 加好友    发送短消息息给shunli 发消息
发表于: IP:您无权察看 2015-7-20 14:24:12 | [全部帖] [楼主帖] 楼主

UNPIVOT运算符相对于PIVOT运算符,它执行与PIVOT相反的操作,即将列转换到行。需要注意的是UNPIVOT运算符并不完全是PIVOT的逆向操作。因为执行PIVOT将会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 无法重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT 的输入中的空值不会显示在输出中,而在执行 PIVOT 操作之前,输入中可能有原始的空值。

一、UNPIVOT的语法

SELECT
[DataType]
,   [DataValue]
,   [UnpivotedCol1]
,   [UnpivotedCol2]
,   [UnpivotedCol3]
FROMtable
UNPIVOT
(
[DataValue] FOR [DataType]
IN ([UnpivotedCol1],[UnpivotedCol1],[UnpivotedCol3],etc..)
)
AS Alias
--需要注意的是,对于每一个UNPIVOT列值,必须使用中括号括起来。

二、UNPIVOT的使用例子

1.静态UNPIVOT的用法

USE tempdb
GO
SELECTYEAR(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 FORYearIN ([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
,CASEWHEN Quantity = 0 THENNULLELSE Quantity ENDAS 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 FORYearIN ([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 NOTIN(N'CustomerID')
) AS x
ORDERBY x
FOR XML PATH('')),
1,1,N'');
SET @UnpivotTableSQL =
'SELECT CustomerID
,Year
,CASEWHEN Quantity = 0 THENNULLELSE Quantity ENDAS 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 FORYearIN (' + @UnpivotColHeader + ')
)  x
)y' + N';';
EXEC sp_executesql @UnpivotTableSQL;
--结果同上


三、总结

1.UNPIVOT操作符的第一个输入是保存被旋转属性值(Quantity)的目标列名称。

2.FOR关键字,指定保存被旋转列名称(Year)的目标列名称。

3.在IN子句的圆括号,指定要旋转的源列名称([1996],[1997],[1998])。

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论