1.
=================================================
http://untitled.spaces.live.com/blog/cns!86b82838704e0d5a!783.entry
微软SQL Server 2005语法增强之PIVOT
使用 PIVOT 和 UNPIVOT
可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。
注意:
对升级到 Microsoft SQL Server 2005 的数据库使用 PIVOT 和 UNPIVOT 时,数据库的兼容级别必须设置为 90。有关如何设置数据库兼容级别的信息,请参阅 sp_dbcmptlevel (Transact-SQL) [ http://msdn2.microsoft.com/zh-cn/library/ms178653(printer).aspx ]。 |
PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关 PIVOT 语法的完整说明,请参阅 FROM (Transact-SQL) [ http://msdn2.microsoft.com/zh-cn/library/ms177634(printer).aspx ]。
以下是带批注的 PIVOT 语法。
SELECT <non-pivoted column>,
[first pivoted column] AS <column name> ,
[second pivoted column] AS <column name> ,
...
[last pivoted column] AS <column name>
FROM
( <SELECT query that produces the data> );
AS <alias for the source query>
PIVOT
(
<aggregation function>( <column being aggregated> )
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column] , [second pivoted column] ,
... [last pivoted column] )
) AS <alias for the pivot table>
<optional ORDER BY clause>
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>
简单 PIVOT 示例
下面的代码示例生成一个两列四行的表。
USE AdventureWorks;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture
下面是结果集:
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
没有定义 DaysToManufacture 为 3 的产品。
以下代码显示相同的结果,该结果经过透视以使 DaysToManufacture 值成为列标题。提供一个列表示三 [3] 天,即使结果为 NULL。
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable
下面是结果集:
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
复杂 PIVOT 示例
可能会用到 PIVOT 的常见情况是:需要生成交叉表格报表以汇总数据。例如,假设需要在 AdventureWorks 示例数据库中查询 PurchaseOrderHeader 表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表(按供应商排序)。
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
以下为部分结果集。
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
将在 EmployeeID 列上透视此嵌套 select 语句返回的结果。
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader
这意味着 EmployeeID 列返回的唯一值自行变成了最终结果集中的字段。结果,在透视子句中指定的每个 EmployeeID 号都有相应的一列:在本例中为雇员 164、198、223、231 和 233。PurchaseOrderID 列作为值列,将根据此列对最终输出中返回的列(称为分组列)进行分组。在本例中,通过 COUNT 函数聚合分组列。请注意,将显示一条警告消息,指出为每个雇员计算 COUNT 时未考虑显示在 PurchaseOrderID 列中的任何空值。
重要提示:
如果聚合函数与 PIVOT 一起使用,则计算聚合时将不考虑出现在值列中的任何空值。 |
UNPIVOT 将与 PIVOT 执行几乎完全相反的操作,将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt,并且您需要将列标识符 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋转为对应于特定供应商的行值。这意味着必须标识另外两个列。包含要旋转的列值(Emp1、Emp2...)的列将被称为 Employee,将保存当前位于待旋转列下的值的列被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_column 和 value_column。以下为该查询。
--Create the table and insert values as portrayed in the previous example.
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, 4)
INSERT INTO pvt VALUES (2, 4, 1, 5, 5, 5)
INSERT INTO pvt VALUES (3, 4, 3, 5, 4, 4)
INSERT INTO pvt VALUES (4, 4, 2, 5, 5, 4)
INSERT INTO pvt VALUES (5, 5, 1, 5, 5, 5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
以下为部分结果集。
VendorID Employee Orders
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
...
请注意,UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT 的输入中的空值不会显示在输出中,而在执行 PIVOT 操作之前,输入中可能有原始的空值。
AdventureWorks 示例数据库中的 Sales.vSalesPersonSalesByFiscalYears 视图将使用 PIVOT 返回每个销售人员在每个会计年度的总销售额。若要在 SQL Server Management Studio 中编写视图脚本,请在“对象资源管理器”中,在“视图”文件夹下找到 AdventureWorks 数据库对应的视图。右键单击该视图名称,再选择“编写视图脚本为”。
以上内容摘自MSDN。
实际工作中,我使用的代码为:
SELECT p1.InvestmentId AS r_InvestmentId,
p1.InvestmentType AS r_InvestmentType,
p1.InvestmentName AS r_InvestmentName,
p1.ExchangeId AS r_ExchangeId,
p1.TickerSymbol AS r_TickerSymbol,
p1.CUSIP AS r_CUSIP,
p1.WKN AS r_WKN,
p1.VALOR AS r_VALOR,
p1.XS AS r_XS,
p1.MEX AS r_MEX,
p1.RUN AS r_RUN,
p1.AWKN AS r_AWKN,
p1.SEDOL AS r_SEDOL,
p1.ExternalId AS r_ExternalId,
p2.PerformanceId AS r_PerformanceId,
p2.ISIN AS r_ISIN,
p2.Oslobors AS r_Oslobors,
p2.PMI AS r_PMI,
p2.PPM AS r_PPM
FROM ( SELECT *
FROM ( SELECT i.InvestmentId,
i.InvestmentType,
i.InvestmentName,
i.ExchangeId,
i.TickerSymbol,
tm.IdentifierName,
ili.Identifier
FROM InvestmentSearch i
INNER JOIN InvestmentIdLevelIdentifier ili ON i.InvestmentId = ili.InvestmentId
INNER JOIN IdTypeMapping tm ON ili.IdentifierType = tm.IdentifierType
AND tm.TypeId = 0
WHERE i.InvestmentType <> 'FD'
UNION
SELECT i.InvestmentId,
i.InvestmentType,
i.InvestmentName,
i.ExchangeId,
i.TickerSymbol,
tm.IdentifierName,
ei.Identifier
FROM dbo.InvestmentSearch i
LEFT JOIN dbo.InvestmentIdLevelExternalIdentifier ei ON i.InvestmentId = ei.InvestmentId
LEFT JOIN dbo.IdTypeMapping tm ON tm.IdentifierType = ei.IdentifierType
AND tm.TypeId = 1
WHERE i.InvestmentType <> 'FD'
) t1 PIVOT (MAX (Identifier) FOR IdentifierName IN ([CUSIP],
[WKN],
[VALOR],
[XS],
[MEX],
[RUN],
[AWKN],
[SEDOL],
[ExternalId]) ) t2
) p1
INNER JOIN
( SELECT *
FROM ( SELECT ep.InvestmentId,
ep.ExchangeId,
ep.TickerSymbol,
ep.PerformanceId,
tm.IdentifierName,
p.Identifier
FROM dbo.EquityPerformanceIdMapping ep
LEFT JOIN dbo.PerfIdLevelIdentifier p ON ep.PerformanceId = p.PerformanceId
LEFT JOIN dbo.IdTypeMapping tm ON tm.IdentifierType = p.IdentifierType
) t1 PIVOT (MAX (Identifier) FOR IdentifierName IN ([ISIN],
[Oslobors],
[PMI],
[PPM]) ) t2
) p2 ON p1.InvestmentId = p2.InvestmentId
2.
=================================================
http://hi.baidu.com/pard/blog/item/d5f5b6458a798226cefca366.html
SQL 2005中pivot and unpivot的用法
2006年07月13日 星期四 20:43
1.Pivot的用法體會: 語句範例: select PN,[2006/5/30] as [20060530],[2006/6/2] as [20060602] from consumptiondata a Pivot (sum(a.M_qty) FOR a.M_date in ([2006/5/30],[2006/6/2])) as PVT order by PN
Table結構 Consumptiondata (PN,M_Date,M_qty) order by PN可要可不要,並不重要,只是排序的作用
關鍵的是紅色部分,解析如下,select大家都知道,PN是 ConsumptionData表中的一個Column, [2006/5/30]也是一個Column,他需要顯示成[20060530],注意[2006/5/30]不是一個Value,而是一個Column.[2006/6/2]與[2006/5/30]一樣. Pivot ( ........... ) as PVT 這個結構是固定格式,沒有什麼需要特殊說明的,當然PVT隨便你給他一個 NICKNAME ,it doesn't make any differences. sum(a.M_qty) 是我們希望顯示出來的值,注意這個地方必須用彙總函數,否則語法不會過. FOR a.M_date in ([2006/5/30],[2006/6/2])for 表示彙總的值要顯示在哪一個Column下面 如果我們想讓Sum(M_qty)顯示在PN轉換的Column下面,則可寫為For PN, in 的清單表示我們關注哪些要查看的Column,注意再次強調是Column,不是Value. in的清單是Column清單,不是Value清單,是M_date的Value轉換成的Column清單.
2.UnPivot --此段可以直接在Sql 2005中執行 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,4) INSERT INTO pvt VALUES (2,4,1,5,5,5) INSERT INTO pvt VALUES (3,4,3,5,4,4) INSERT INTO pvt VALUES (4,4,2,5,5,4) INSERT INTO pvt VALUES (5,5,1,5,5,5) GO --select * from PVT --Unpivot the table. SELECT VendorID, Employee, Orders FROM PVT UNPIVOT ( Orders FOR Employee IN([Emp1], [Emp2], [Emp3], [Emp4], [Emp5]) )AS unpvt GO
|
3.
=================================================
http://blog.csdn.net/cnming/archive/2008/01/23/2060977.aspx
function StorePage(){d=document;t=d.selection?(d.selection.type!='None'?d.selection.createRange().text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://www.365key.com/storeit.aspx?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t),'keyit','scrollbars=no,width=475,height=575,left=75,top=20,status=no,resizable=yes'));keyit.focus();}
SQL Server2005引入了很多迎合开发者口味的新特性,虽然改动不大,却大大了减少了开发者的工作量,这种替用户考虑的开发思路,值得称赞。
在SQL Server2000中,要实现行列转换,需要综合利用聚合函数和动态SQL,实现起来需要一定的技巧,所以在CSDN的SQL讨论区里可以看到大量询问行列转换如何实现的问题。到了2005中,使用新引进的关键字PIVOT/UNPIVOT,可以轻松实现行列转换的需求。
好像Oracle11g也准备引入PIVOT/UNPIVOT特性,对于Oracle开发来说,It's a good news。
本文通过两个简单的例子展示PIVOT/UNPIVOT的用法。详细的语法请参考联机帮助。
PIVOT
创建测试表,插入测试数据
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 row(s) affected)
利用PIVOT将个季度的利润转成横向显示:
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
UNPIVOT
建立测试表,插入测试数据
drop table test
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test values(1,'a',1000,2000,4000,5000)
insert into test values(2,'b',3000,3500,4200,5500)
select * from test
id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
利用UNPIVOT,将同一行中四个季度的列数据转换成四行数据:
select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500
(8 row(s) affected)
4.
=================================================
http://www.diybl.com/course/7_databases/sql/sql2005/2008126/97596.html
<clk></clk>SQL Server2005引入了很多迎合<nobr oncontextmenu="return false;" onmousemove="kwM(3);" id="clickeyekey3" onmouseover="kwE(event,3, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC(event,3)" onmouseout="kwL(event,this)" target="_blank">开发</nobr>者口味的新特性,虽然改动不大,却大大了减少了开发者的工作量,这种替用户考虑的开发思路,值得称赞。
<clk></clk>在SQL Server2000中,要实现行列转换,需要综合利用聚合函数和动态SQL,实现起来需要一定的技巧,所以在CSDN的SQL讨论区里可以看到大量询问行列转换如何实现的问题。到了2005中,使用新引进的关键字PIVOT/UNPIVOT,可以轻松实现行列转换的<nobr oncontextmenu="return false;" onmousemove="kwM(8);" id="clickeyekey8" onmouseover="kwE(event,8, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC(event,8)" onmouseout="kwL(event,this)" target="_blank">需求</nobr>。
好像Oracle11g也准备引入PIVOT/UNPIVOT特性,对于Oracle开发来说,It''s a good news。
<clk></clk>本文<nobr oncontextmenu="return false;" onmousemove="kwM(10);" id="clickeyekey10" onmouseover="kwE(event,10, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC(event,10)" onmouseout="kwL(event,this)" target="_blank">通过</nobr>两个简单的例子展示PIVOT/UNPIVOT的用法。详细的语法请参考联机帮助。
PIVOT
创建测试表,插入测试数据
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,''a'',1,1000)
insert into test values(1,''a'',2,2000)
insert into test values(1,''a'',3,4000)
insert into test values(1,''a'',4,5000)
insert into test values(2,''b'',1,3000)
insert into test values(2,''b'',2,3500)
insert into test values(2,''b'',3,4200)
insert into test values(2,''b'',4,5500)
select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 row(s) affected)
<clk></clk>利用PIVOT将个季度的<nobr oncontextmenu="return false;" onmousemove="kwM(5);" id="clickeyekey5" onmouseover="kwE(event,5, this);" style="COLOR: #6600ff; BORDER-BOTTOM: #6600ff 1px dotted; BACKGROUND-COLOR: transparent; TEXT-DECORATION: underline" onclick="return kwC(event,5)" onmouseout="kwL(event,this)" target="_blank">利润</nobr>转成横向显示:
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
UNPIVOT
建立测试表,插入测试数据
drop table test
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test values(1,''a'',1000,2000,4000,5000)
insert into test values(2,''b'',3000,3500,4200,5500)
select * from test
id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
利用UNPIVOT,将同一行中四个季度的列数据转换成四行数据:
select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500
(8 row(s) affected)
分享到:
相关推荐
主要介绍了SQLServer行列互转实现思路,使用聚合函数pivot/unpivot实现行列互转,感兴趣的小伙伴们可以参考一下
SQL行列转换 Pivot UnPivot
枢纽表达式PIVOT和UNPIVOT的简单用法 sql 列转成行 sql server 2005
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
动态 实现 SQL 2008行列转换的pivot
此文档中详细的记载了,SQL Server 2005新功能之PIVOT的描述,希望可以帮到下载的朋友们!
Oracle 11g中的pivot和unpivot转换操作
它们模仿 T_SQL 透视/反透视命令或电子表格软件的透视功能。 如果您有大量由两个(或更多)特征描述的值,则此功能允许您按这两个特征对这些值进行分组。 例如,您可以将任何员工在过去 10 个月内销售的总金额...
说明:sql server如何通过pivot对数据进行行列转换 */ create table #t ( [员工工号] nvarchar(50), [员工姓名] nvarchar(50), [月份] nvarchar(50), [工资] int ) insert into #t values(N'01', N'张三', N'...
PowerPivot 与 Excel 和 SharePoint 集成。在 Excel 环境中,PowerPivot for Excel 提供熟悉的工作站式的创作和分析体验。在 SharePoint 场中,PowerPivot for SharePoint 添加了服务器端应用程序和功能,支持对您...
SQL语句行列转换(附带数据库、表、视图操作) ,不错的文档。
昨天遇到一个SQL Server的问题:需要写一个储存过程来处理几个表中的数据,最后问题出在我想将一个表的一个列的多行内容拼接成一行,比如表中有两列数据 : 类别 名称 AAA 企业1 AAA 企业2 AAA 企业3 ...
很多书籍包括教材介绍pivot语句不详细,难以理解其查询结果,这样势必会造成使用该语句时有困难,本文理论讲解透彻,结合例题分析,最后给出实验题目让你练手,以尽快掌握其使用。
博客文章地址:http://blog.csdn.net/pfe_nova/article/details/41357435 SQL2005之后添加的几个排名函数以及透析与反透析关键字,主要有Row_Number、RANK、DENSE_RANK、NTILE函数,pivot以及unpivot关键字
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
T-SQL语句中,Pivot运算符用于在列和行之间对数据进行旋转或透视转换,PIVOT命令可以实现数据表的列转行,同时执行聚合运算,UNPIVOT则与其相反,实现数据的行转列。
If youre a database or data warehouse developer, this is the expert resource you need to build full-scale, multi-dimensional, database applications using Microsofts new "SQL Server 2012 Analysis ...
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...