|
.Pivot<span style="font-family: 宋体">的用法体会: <span style="font-family: 宋体">语句范例: <span style="color: red">select PN,[2006/5/30] as [20060530],[2006/6/2] as [20060602]
from consumptiondata a <span style="color: red">Pivot (sum(a.M_qty) FOR a.M_date in ([2006/5/30],[2006/6/2])) as PVT
order by PN
Table<span style="font-family: 宋体">结构 Consumptiondata (PN,M_Date,M_qty)
order by PN<span style="font-family: 宋体">可要可不要,<span style="font-family: 宋体">并不重要,<span style="font-family: 宋体">只是排序的作用
<span style="font-family: 宋体">关键的是红色部分,<span style="font-family: 宋体">解析如下,select<span style="font-family: 宋体">大家都知道,PN<span style="font-family: 宋体">是 ConsumptionData<span style="font-family: 宋体">表中的一个Column,
[2006/5/30]<span style="font-family: 宋体">也是一个Column,<span style="font-family: 宋体">他需要显示成[20060530],<span style="font-family: 宋体">注意[2006/5/30]<span style="font-family: 宋体">不是一个Value,<span style="font-family: 宋体">而是一个Column.[2006/6/2]<span style="font-family: 宋体">与[2006/5/30]<span style="font-family: 宋体">一样. <span style="color: red">Pivot ( ........... ) as PVT<span style="color: black; font-family: 宋体">这个结构是固定格式<span style="color: black">,<span style="color: black; font-family: 宋体">没有什么需要特殊说明的<span style="color: black">,<span style="color: black; font-family: 宋体">当然<span style="color: black">PVT<span style="color: black; font-family: 宋体">随便你给他一个<span style="color: black"> NICKNAME,it doesn't make any differences. <span style="color: red">sum(a.M_qty) <span style="font-family: 宋体">是我们希望显示出来的值,<span style="font-family: 宋体">注意这个地方必须用汇总函数,<span style="font-family: 宋体">否则语法不会过. <span style="color: red">FOR a.M_date in ([2006/5/30],[2006/6/2])<span style="color: black">for <span style="color: black; font-family: 宋体">表示汇总的值要显示在哪一个<span style="color: black">Column<span style="color: black; font-family: 宋体">下面 <span style="font-family: 宋体">如果我们想让Sum(M_qty)<span style="font-family: 宋体">显示在PN<span style="font-family: 宋体">转换的Column<span style="font-family: 宋体">下面,<span style="font-family: 宋体">则可写为For PN,in <span style="font-family: 宋体">的清单表示我们关注哪些要查看的Column,<span style="font-family: 宋体">注意再次强调是Column,<span style="font-family: 宋体">不是Value. in<span style="font-family: 宋体">的清单是Column<span style="font-family: 宋体">清单,<span style="font-family: 宋体">不是Value<span style="font-family: 宋体">清单,<span style="font-family: 宋体">是M_date<span style="font-family: 宋体">的Value<span style="font-family: 宋体">转换成的Column<span style="font-family: 宋体">清单.
2.UnPivot
--<span style="font-family: 宋体">此段可以直接在Sql 2005<span style="font-family: 宋体">中执行 <span style="color: blue">CREATE TABLE pvt (VendorID int,Emp1 int,Emp2 int,
Emp3 int,Emp4 int,Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4)
INSERT INTO pvt VALUES (2,1,5)
INSERT INTO pvt VALUES (3,4)
INSERT INTO pvt VALUES (4,2,4)
INSERT INTO pvt VALUES (5,5)
GO <span style="color: blue">--select * from PVT <span style="color: blue">--Unpivot the table.
SELECT VendorID,Employee,Orders
FROM PVT<span style="color: red">UNPIVOT ( <span style="color: blue">Orders FOR Employee IN([Emp1],[Emp2],[Emp3],[Emp4],[Emp5])<span style="color: red">)AS unpvt <span style="color: blue">GO
<span style="font-family: 宋体">说明: Employee<span style="font-family: 宋体">以 Column [Emp1]...<span style="font-family: 宋体">为值,Orders<span style="font-family: 宋体">以Column [Emp1]...<span style="font-family: 宋体">的值为值 （编辑：商洛站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
