unpivot的用法和样例：
UNPIVOT does not reproduce the original table-valued expression result because rows have been merged.
而UNPIVOT不会重现原始表值表达式的结果,因为行已经被合并了。
UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows.
UNPIVOT将与PIVOT执行几乎完全相反的操作,将列转换为行。
unpivot的海词问答与网友补充：
unpivot的相关资料：
相关词典网站：Pivot 和 Unpivot
来源：博客园
在TSQL中，使用Pivot和Unpivot运算符将一个关系表转换成另外一个关系表，两个命令实现的操作是“相反”的，但是，pivot之后，不能通过unpivot将数据还原。这两个运算符的操作数比较复杂，记录一下自己的总结，以后用到时，作为参考。
一，Pivot用法
Pivot旋转的作用，是将关系表（table_source）中的列（pivot_column）的值，转换成另一个关系表（pivot_table）的列名：

table_source
pivot
(
aggregation_function（aggregated_column）
for pivot_column in ([pivot_column_value_list])
) as pivot_table_alias

透视操作的处理流程是：
对pivot_column和 aggregated_column的其余column进行分组，即，group by other_columns；
当pivot_column值等于某一个指定值，计算aggregated_column的聚合值；
在使用透视命令时，需要注意：
pivot将table_source旋转成透视表（pivot_table）之后，不能再被引用
pivot_column的列值，必须使用中括号（[]）界定符
必须显式命名pivot_table的别名
1，创建示例数据

use tempdb
go 

drop table if exists dbo.usr
go

create table dbo.usr
(
name varchar(10),
score int,
class varchar(8)
)
go

insert into dbo.usr
values('a',20,'math'),('b',21,'math'),('c',22,'phy'),('d',23,'phy')
,('a',22,'phy'),('b',23,'phy'),('c',24,'math'),('d',25,'math')
go

View Code

2，对name进行分组，对score进行聚合，将class列的值转换为列名

select p.name,p.math,p.phy
from dbo.usr u
pivot
(
sum(score)
for class in([math],[phy]) 
) as p


3，pivot的等价写法：使用case when语句实现
pivot命令的执行流程很简单，使用caseh when子句实现pivot的功能

select u.name,
sum(case when u.class='math' then u.score else null end) as math,
sum(case when u.class='phy' then u.score else null end) as phy
from dbo.usr u
group by u.name

使用group by子句对name列分组，使用 case when 语句将pivot_column的列值作为列名返回，并对aggregated_column计算聚合值。
4，动态Pivot写法
静态pivot写法的弊端是：如果pivot_column的列值发生变化，静态pivot不能对新增的列值进行透视，变通方法是使用动态sql，拼接列值
Script1，使用case-when子句实现

declare @sql nvarchar(max)
declare @columnlist nvarchar(max)

set @columnlist=N''

;with cte as
(
select distinct class
from dbo.usr
)
select @columnlist+='sum(case when u.class='''+cast(class as varchar(10))+N''' then u.score else null end) as ['+cast(class as varchar(10))+N'],'
from cte

select @columnlist=SUBSTRING(@columnlist,1,len(@columnlist)-1)

select @sql=
N'select u.name,'
+@columnlist
+N'from dbo.usr u
group by u.name'

exec(@sql)

View Code
Script2，使用pivot子句实现

declare @sql nvarchar(max)
declare @classlist nvarchar(max)

set @classlist=N''

;with cte as
(
select distinct class
from dbo.usr
)
select @classlist+=N'['+cast(class as varchar(11))+N'],'
from cte

select
@classlist=SUBSTRING(@classlist,1,len(@classlist)-1)

select @sql=N'select p.name,'+@classlist+
N' from dbo.usr u
PIVOT
(
sum(score) 
for class in('+@classlist+N')
) as p'

exec (@sql)

View Code
二，Unpivot用法
unpivot是将列名转换为列值，列名做为列值，因此，会新增两个column：一个column用于存储列名，一个column用于存储列值

table_soucr
unpivot
(
newcolumn_store_unpivotcolumn_name for 
newcolumn_store_unpivotcolumn_value in (unpivotcolumn_name_list)

)

逆透视（unpivot）的处理流程是：
unpivotcolumn_name_list是逆透视列的列表，其列值是相兼容的，能够存储在一个column中
保持其他列（除unpivotcolumn_name_list之外的所有列）的列值不变
依次将unpivotcolumn的列名存储到newcolumn_store_unpivotcolumn_name字段中，将unpivotcolumn的列值存储到newcolumn_store_unpivotcolumn_value字段中
1，创建示例数据

CREATE TABLE dbo.Venders 
(
VendorID int, 
Emp1 int, 
Emp3 int, 
Emp4 int, 
Emp5 int
);

GO 
 
INSERT INTO dbo.Venders VALUES (1,4,3,5,4,4);

INSERT INTO dbo.Venders VALUES (2,4,1,5,5,5);

INSERT INTO dbo.Venders VALUES (3,4,3,5,4,4);

INSERT INTO dbo.Venders VALUES (4,4,2,5,5,4);

INSERT INTO dbo.Venders VALUES (5,5,1,5,5,5);

GO 

View Code

2，unpivot用法示例
将Emp1, Emp2, Emp3, Emp4, Emp5的列名和列值存储到字段：Employee和Orders中

SELECT VendorID, Employee, Orders

FROM dbo.Venders as p 
UNPIVOT

(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)

GO 

3，unpivot可以使用union all来实现

select VendorID, 'Emp1' as Employee, Emp1 as Orders
from dbo.Venders
union all 
select VendorID, 'Emp2' as Employee, Emp2 as Orders
from dbo.Venders
union all 
select VendorID, 'Emp3' as Employee, Emp3 as Orders
from dbo.Venders
union all
select VendorID, 'Emp4' as Employee, Emp4 as Orders
from dbo.Venders
union all
select VendorID, 'Emp5' as Employee, Emp5 as Orders
from dbo.Venders

View Code
4，动态unpivot的实现，使用动态sql语句
聪明如你，应该很容易就实现....
三，性能讨论
pivot和unpivot的性能不是很好，不要用来处理海量的数据
 
参考文档：
免责声明：本站部分内容、图片、文字、视频等来自于互联网，仅供大家学习与交流。相关内容如涉嫌侵犯您的知识产权或其他合法权益，请向本站发送有效通知，我们会及时处理。反馈邮箱&&&&。
学生服务号
在线咨询，奖学金返现，名师点评，等你来互动Oracle 数据库 11g： 面向 DBA 和开发人员的重要新特性Pivot 和 Unpivot
Pivot 和 Unpivot使用简单的 SQL 以电子表格类型的交叉表报表显示任何关系表中的信息，并将交叉表中的所有数据存储到关系表中。下载 Oracle 数据库 11g
Pivot如您所知，关系表是表格化的，即，它们以列-值对的形式出现。假设一个表名为 CUSTOMERS。SQL& desc customers
----------------------------------------- -------- ---------------------------
NUMBER(10)
VARCHAR2(20)
STATE_CODE
VARCHAR2(2)
TIMES_PURCHASED
选定该表：
select cust_id, state_code, times_purchased
from customers
order by cust_
输出结果如下：
CUST_ID STATE_CODE TIMES_PURCHASED
------- ---------- ---------------
and so on ...
注意数据是如何以行值的形式显示的：针对每个客户，该记录显示了客户所在的州以及该客户在商店购物的次数。当该客户从商店购买更多物品时，列 times_purchased 会进行更新。现在，假设您希望统计一个报表，以了解各个州的购买频率，即，各个州有多少客户只购物一次、两次、三次等等。如果使用常规 SQL，您可以执行以下语句：select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_
输出如下：
ST TIMES_PURCHASED
-- --------------- ----------
and so on ...
这就是您所要的信息，但是看起来不太方便。使用交叉表报表可能可以更好地显示这些数据，这样，您可以垂直排列数据，水平排列各个州，就像电子表格一样：
Times_purchased
and so on ...
and so on ...
在 Oracle 数据库 11g 推出之前，您需要针对每个值通过 decode 函数进行以上操作，并将每个不同的值编写为一个单独的列。但是，该方法一点也不直观。庆幸的是，您现在可以使用一种很棒的新特性 PIVOT 通过一种新的操作符以交叉表格式显示任何查询，该操作符相应地称为 pivot。下面是查询的编写方式：select * from (
select times_purchased, state_code
from customers t
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
order by times_purchased
输出如下：
. TIMES_PURCHASED
--------------- ---------- ---------- ---------- ---------- ----------
... and so on ...
这表明了 pivot 操作符的威力。state_codes 作为标题行而不是列显示。下面是传统的表格化格式的图示：
图 1 传统的表格化显示
在交叉表报表中，您希望将 Times Purchased 列的位置掉换到标题行，如图 2 所示。该列变为行，就好像该列逆时针旋转 90 度而变为标题行一样。该象征性的旋转需要有一个支点 (pivot point)，在本例中，该支点为 count(state_code) 表达式。
图 2 执行了 Pivot 操作的显示
该表达式需要采用以下查询语法：
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
第二行“for state_code ...”限制查询对象仅为这些值。该行是必需的，因此不幸的是，您需要预先知道可能的值。该限制在 XML 格式的查询将有所放宽，如本文后面部分所述。
注意输出中的标题行：. TIMES_PURCHASED
--------------- ---------- ---------- ---------- ---------- ----------
列标题是来自表本身的数据：州代码。缩写可能已经相当清楚无需更多解释，但是假设您希望显示州名而非缩写（“Connecticut”而非“CT”），那又该如何呢？如果是这样，您需要在查询的 FOR 子句中进行一些调整，如下所示：
select * from (
select times_purchased as &Puchase Frequency&, state_code
from customers t
count(state_code)
for state_code in ('NY' as &New York&,'CT' &Connecticut&,
'NJ' &New Jersey&,'FL' &Florida&,'MO' as &Missouri&)
order by 1
Puchase Frequency
New York Connecticut New Jersey
----------------- ---------- ----------- ---------- ---------- ----------
and so on ...
FOR 子句可以提供其中的值（这些值将成为列标题）的别名。
Unpivot就像有物质就有反物质一样，有 pivot 就应该有“unpivot”，对吧？好了，不开玩笑，但 pivot 的反向操作确实需要。假设您有一个显示交叉表报表的电子表格，如下所示：Purchase FrequencyNew YorkConnecticutNew JerseyFloridaMissouri
190014229878
2866781339
现在，您希望将这些数据加载到一个名为 CUSTOMERS 的关系表中： SQL& desc customers
----------------------------------------- -------- ---------------------------
NUMBER(10)
VARCHAR2(20)
STATE_CODE
VARCHAR2(2)
TIMES_PURCHASED
必须将电子表格数据去规范化为关系格式，然后再进行存储。当然，您可以使用 DECODE 编写一个复杂的 SQL*:Loader 或 SQL 脚本，以将数据加载到 CUSTOMERS 表中。或者，您可以使用pivot 的反向操作 UNPIVOT，将列打乱变为行，这在 Oracle 数据库 11g 中可以实现。
通过一个示例对此进行演示可能更简单。让我们首先使用 pivot 操作创建一个交叉表：
create table cust_matrix
select * from (
select times_purchased as &Puchase Frequency&, state_code
from customers t
count(state_code)
for state_code in ('NY' as &New York&,'CT' &Conn&,
'NJ' &New Jersey&,'FL' &Florida&,
'MO' as &Missouri&)
12* order by 1
您可以查看数据在表中的存储方式：
SQL& select * from cust_matrix
Puchase Frequency
Conn New Jersey
----------------- ---------- ---------- ---------- ---------- ----------
... and so on ...
这是数据在电子表格中的存储方式：每个州是表中的一个列（“New York”、“Conn”等等）。
SQL& desc cust_matrix
----------------------------------------- -------- ---------------------------
Puchase Frequency
New Jersey
您需要将该表打乱，使行仅显示州代码和该州的购物人数。通过 unpivot 操作可以达到此目的，如下所示：
from cust_matrix
state_counts
for state_code in (&New York&,&Conn&,&New Jersey&,&Florida&,&Missouri&)
order by &Puchase Frequency&, state_code
输出如下：
Puchase Frequency STATE_CODE STATE_COUNTS
----------------- ---------- ------------
1 Missouri
1 New Jersey
1 New York
2 Missouri
and so on ...
注意每个列名如何变为 STATE_CODE 列中的一个值。Oracle 如何知道 state_code 是一个列名？它是通过查询中的子句知道的，如下所示：
for state_code in (&New York&,&Conn&,&New Jersey&,&Florida&,&Missouri&)
这里，您指定“New York”、“Conn”等值是您要对其执行 unpivot 操作的 state_code 新列的值。我们来看看部分原始数据：
Puchase Frequency
Conn New Jersey
----------------- ---------- ---------- ---------- ---------- ----------
当列“纽约”突然变为一个行中的值时，您会怎样显示&#2 呢？该值应该显示在哪一列下呢？上述查询中 unpivot 操作符内的 for 子句上面的子句对此进行了解答。您指定了 state_counts，它就是在生成的输出中创建的新列的名称。Unpivot 可以是 pivot 的反向操作，但不要以为前者可以对后者所进行的任何操作进行反向操作。例如，在上述示例中，您对 CUSTOMERS 表使用pivot 操作创建了一个新表 CUST_MATRIX。然后，您对 CUST_MATRIX 表使用了
unpivot，但这并没有取回原始表 CUSTOMERS 的详细信息。相反，交叉表报表以便于您将数据加载到关系表中的不同方式显示。因此unpivot 并不是为了取消
pivot 所进行的操作。在使用 pivot 创建一个表然后删除原始表之前，您应该慎重考虑。unpivot 的某些很有趣的用法超出了通常的强大数据操作功能范围（如上面的示例）。Amis Technologies 的 Oracle ACE 总监 Lucas Jellema 介绍了如何生成若干行特定数据用于测试。在此，我将对他的原始代码稍加修改，以显示英语字母表中的元音：select value
for value_type in
(v1,v2,v3,v4,v5)
输出如下：
该模型可以扩展为包含任何类型的行生成器。感谢 Lucas 为我们提供了这一巧妙招术。
XML 类型在上述示例中，注意您指定有效的 state_codes 的方式：for state_code in ('NY','CT','NJ','FL','MO')
该要求假设您知道 state_code 列中显示的值。如果您不知道都有哪些值，您怎么构建查询呢？
pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出，在此输出中，您可以指定一个特殊的子句 ANY 而非文字值。示例如下：select * from (
select times_purchased as &Purchase Frequency&, state_code
from customers t
count(state_code)
for state_code in (any)
order by 1
输出恢复为 CLOB 以确保 LONGSIZE 在查询运行之前设置为大值。
&SQL& set long 99999
较之原始的 pivot 操作，该查询有两处明显不同（用粗体显示）。首先，您指定了一个子句 pivot xml 而不只是 pivot。该子句生成 XML 格式的输出。其次，for 子句显示 for state_code in (any) 而非长列表的 state_code 值。该 XML 表示法允许您使用 ANY 关键字，您不必输入 state_code 值。输出如下：Purchase Frequency STATE_CODE_XML
------------------ --------------------------------------------------
1 &PivotSet&&item&&column name = &STATE_CODE&&CT&/co
lumn&&column name = &COUNT(STATE_CODE)&&165&/colum
n&&/item&&item&&column name = &STATE_CODE&&NY&/col
umn&&column name = &COUNT(STATE_CODE)&&33048&/colu
mn&&/item&&/PivotSet&
2 &PivotSet&&item&&column name = &STATE_CODE&&CT&/co
lumn&&column name = &COUNT(STATE_CODE)&&179&/colum
n&&/item&&item&&column name = &STATE_CODE&&NY&/col
umn&&column name = &COUNT(STATE_CODE)&&33151&/colu
mn&&/item&&/PivotSet&
... and so on ...
如您所见，列 STATE_CODE_XML 是 XMLTYPE，其中根元素是 &PivotSet&。每个值以名称-值元素对的形式表示。您可以使用任何 XML 分析器中的输出生成更有用的输出。
除了 ANY 子句外，您还可以编写一个子查询。假设您有一个优先州列表并希望仅选择这些州的行。您将优先州放在一个名为 preferred_states 的新表中：SQL& create table preferred_states
state_code varchar2(2)
Table created.
SQL& insert into preferred_states values ('FL')
1 row created.
Commit complete.
现在 pivot 操作如下所示：
select * from (
select times_purchased as &Puchase Frequency&, state_code
from customers t
count(state_code)
for state_code in (select state_code from preferred_states)
order by 1
for 子句中的子查询可以是您需要的任何内容。例如，如果希望选择所有记录而不限于任何优先州，您可以使用以下内容作为 for 子句：
for state_code in (select distinct state_code from customers)
子查询必须返回不同的值，否则查询将失败。这就是我们指定上述 DISTINCT 子句的原因。
结论Pivot 为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表，而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样，您可以使用unpivot 操作转换任何交叉表报表，以常规关系表的形式对其进行存储。
Pivot 可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出，您不必指定 pivot 操作需要搜索的值域。Forward from /technetwork/cn/articles/11g-pivot-101924-zhs.html
> 本站内容系网友提交或本网编辑转载，其目的在于传递更多信息，并不代表本网赞同其观点和对其真实性负责。如涉及作品内容、版权和其它问题，请及时与本网联系，我们将在第一时间删除内容！
SQL Access Advisor获得有关基于实际频率和使用类型(而非数据类型)进行分区.索引和创建物化视图以改进模式设计的建议.
Oracle 数据库 10g 提供了大量帮助程序(或&顾问程序&),可帮助您决定最佳操作流程.其中一个示例是 SQL Tuning Advisor,它可以提供有关查询调整以及在流程中延长整个优化过程的建 ...
Created By BaoXinjian 一.摘要 在这个由多个部分组成的系列中,通过简单.可操作的方法文档和示例代码,了解这些新特性(例如,数据库重放.闪回数据存档.基于版本的重定义以及 SecureFiles 工作)的重要性.(针对第 2 版进行了更新!) 更改(尽管会不断发生)极少是无风险的.即使更改相对较小(例如,创建索引) ...
原文地址:/technetwork/cn/articles/sql/11g-ha-302502-zhs.html
在 Oracle Database 11g 第 2 版中,安装过程发生了三项重大改动.首先,一个新界面取代了我们熟悉的 Oracle Universal Installer 屏幕. 注意左侧的列表显示 ...
作者:Arup Nanda
Data Guard 了解 Active Data Guard 如何通过实时查询,同时应用归档的的日志.将物理备用数据库转换为快照备用数据库以及对基础架构的一系列改进措施,让您对备份环境的投资物有所值. 下载 Oracle 数据库 11g Oracle 数据库 11g 对 Data Guard 功能进行了多方面的增强,难以详尽 ...
Oracle 11g有多个版本可供选择: 标准版1.标准版和企 业版.所有这些版本都使用相同的通用代码库构建,这意味着企业的数据库管理软件可以轻松地从规模较小的单一处理器服务器扩展到多处理器服务器集 群,而无需更改一行代码.Oracle数据库11g企业版还有许多其他增强了性能.可伸缩性.可用性.安全性和可管理性的功能选项.
主要功能汇总简化版 10g标 ...
问题如下: 1.char,varchar,nvarchar之间的区别 2.MS SQL Server2005支持哪几种访问协议 3.tempdb的作用 4.Select * fron tableName where colName=NULL有什么问题 5.join,in,exists的使用场景及取舍因素 近段时间我使用这几个问题和一些开发人员进行了交流,其中 ...
近日有幸收到清华大学出版社寄来的&&Oracle Database 11g & MySQL 5.6开发手册&&一书,大致翻了一遍,这是一本译作.全书的内容比较适合于Oracle以及MySQL的开发人员作参考.尤其是经常在这两种数据库之间进行切换的初中级开发人员.
该书的原作者Michael McL ...
在 Oracle Enterprise Linux 5 (32 位和 64 位)上安装 Oracle 数据库 11g 第 1 版 作者:John Smiley 学习在 Oracle Enterprise Linux 5 上从头安装 Oracle 数据库 11g 第 1 版的基础知识(仅用于评估). 目录 概述 第 1 部分:安装 Linux 第 2 部分:针 ...随笔 - 49&
文章 - 0&评论 - 3&trackbacks - 0
在mssql中大家都知道可以使用pivot来统计数据，实现像excel的透视表功能
一、MSsqlserver中我们通常的用法
1、Sqlserver数据库测试
---创建测试表
Create table s(
[name] nvarchar(50),
book nvarchar(50),
saledNumber int
----插入测试数据
insert into s ([name],book,saledNumber) values('小王','java从入门到精通',10);
insert into s ([name],book,saledNumber)values('小李','java从入门到精通',15);
insert into s ([name],book,saledNumber)values('小王','C#高级编程',8);
insert into s ([name],book,saledNumber)values('小李','java从入门到精通',7);
insert into s ([name],book,saledNumber)values('小王','java从入门到精通',9);
insert into s ([name],book,saledNumber)values('小李','java从入门到精通',2);
insert into s ([name],book,saledNumber)values('小王','C#高级编程',3);
insert into s ([name],book,saledNumber)values('小李','java从入门到精通',5);
insert into s ([name],book,saledNumber)values('小李','C#高级编程',5);
---1、通过case when 方式
SELECT [name], sum(case
book when 'java从入门到精通' then saledNumber else 0 end) as [java从入门到精通],sum(case
book when 'C#高级编程' then saledNumber else 0 end) as [C#高级编程], sum(saledNumber) as [sum] from s group by [name]
---2、通过pivot
select sa.name,sa.java从入门到精通,sa.C#高级编程 ,sa.java从入门到精通+sa.C#高级编程 as 合计 from s
pivot(sum(saledNumber) for book in (java从入门到精通,C#高级编程)) sa
二、oracle 的用法
---创建测试表
Create table s(
name varchar(50),
book varchar(50),
saledNumber number(9)
----插入测试数据
insert into s (name,book,saledNumber) values('小王','java从入门到精通',10);
insert into s (name,book,saledNumber)values('小李','java从入门到精通',15);
insert into s (name,book,saledNumber)values('小王','C#高级编程',8);
insert into s (name,book,saledNumber)values('小李','java从入门到精通',7);
insert into s (name,book,saledNumber)values('小王','java从入门到精通',9);
insert into s (name,book,saledNumber)values('小李','java从入门到精通',2);
insert into s (name,book,saledNumber)values('小王','C#高级编程',3);
insert into s (name,book,saledNumber)values('小李','java从入门到精通',5);
insert into s (name,book,saledNumber)values('小李','C#高级编程',5);
---1、通过case when 方式
SELECT name, sum(case book when 'java从入门到精通' then saledNumber else 0 end) as java从入门到精通 ,sum(case book when 'C#高级编程' then saledNumber else 0 end) as C#高级编程, sum(saledNumber) as sum
---2、通过pivot
select * from (select name,saledNumber,book from s group by name, saledNumber, book )
pivot(sum(saledNumber) for book in ('java从入门到精通','C#高级编程')) ;
描述不清或者有错误的地方,希望大家指正修改，对于oracle中更多关于Pivot 和 Unpivot
使用简单的 SQL 以电子表格类型的交叉表报表显示任何关系表中的信息，并将交叉表中的所有数据存储到关系表中。请参考oracle官方实例
阅读(...) 评论()