set nocount
on
--> 测试数据:[ta]
if object_id('[ta]')
is not
null drop table
[ta]
go
create table
[ta]([id]
int,[name]
varchar(1))
insert [ta]
select 1,'a'union
all
select 2,'b'union
all
select 3,'c'union
all
select 4,'d'
--> 测试数据:[tb]
if object_id('[tb]')
is not
null drop table
[tb]
go
create table
[tb]([id]
int,[name]
varchar(2))
insert [tb]
select 1,'ff'union
all
select 1,'yy'union
all
select 1,'yf'union
all
select 2,'xx'union
all
select 2,'oo'union
all
select 3,'xo'union
all
select 5,'fy'
--------------开始查询--------------------------
--SQL 2005 新的表运算符apply pivot unpivot
--apply 涉及到下面两个步骤的子集(取决于apply
的类型)
--1、把右表表达式应用到左表输入的行
--2、添加外部行
--使用apply时,就好像先计算左输入,然后为左输入中的每一行计算一次右输入。
--cross apply 和outer apply的区别在于前者的外部(左侧)在内部(右侧)
--找不到对应航的时候不返回,后者则返回null
--看下面的例子
select *
from[ta]
a cross apply(select
* from
tb b where
a.[id]=b.[id]
)c
select *
from[ta]
a outer apply(select
* from
tb b where
a.[id]=b.[id]
)c
--cross apply 很重要的一个应用就是可以用来返回前N行
select c.*from
[ta] a
cross apply(select
top 2 *
from tb b
where a.[id]=b.[id]
order by
b.name
)c
--pivot 包含下面三个逻辑
--1、隐式分组
--2、隔离值
--3、聚合函数
select *
fromtb
pivot(count(id)
for [id]
in([1],[2],[3]))
as p
--unpivot
--UNPIVOT 与PIVOT
执行相反的操作,将表值表达式的列转换为列值。
--UNPIVOT 将与PIVOT
执行几乎完全相反的操作,将列转换为行。
--假设以上示例中生成的表在数据库中存储为pvt,
--并且您需要将列标识符Emp1、Emp2、Emp3、Emp4
和Emp5 旋转为对应于特定供应商的行值。
--这意味着必须标识另外两个列。包含要旋转的列值(Emp1、Emp2...)的列将被称为Employee,
--将保存当前位于待旋转列下的值的列被称为Orders。这些列分别对应于
-- Transact-SQL 定义中的pivot_column
和value_column。以下为该查询。
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
--请注意,UNPIVOT
并不完全是PIVOT
的逆操作。PIVOT 会执行一次聚合,
--从而将多个可能的行合并为输出中的单个行。
--而UNPIVOT
不会重现原始表值表达式的结果,因为行已经被合并了。
--另外,UNPIVOT
的输入中的空值不会显示在输出中,
--而在执行PIVOT
操作之前,输入中可能有原始的空值。
--PIVOT 和UNPIVOT
在语法上可能让人很难理解,不过用得多了就熟悉了也就理解了
分享到:
相关推荐
SQL行列转换 Pivot UnPivot
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
此文档中详细的记载了,SQL Server 2005新功能之PIVOT的描述,希望可以帮到下载的朋友们!
枢纽表达式PIVOT和UNPIVOT的简单用法 sql 列转成行 sql server 2005
PowerPivot 与 Excel 和 SharePoint 集成。在 Excel 环境中,PowerPivot for Excel 提供熟悉的工作站式的创作和分析体验。在 SharePoint 场中,PowerPivot for SharePoint 添加了服务器端应用程序和功能,支持对您...
Oracle 11g中的pivot和unpivot转换操作
很多书籍包括教材介绍pivot语句不详细,难以理解其查询结果,这样势必会造成使用该语句时有困难,本文理论讲解透彻,结合例题分析,最后给出实验题目让你练手,以尽快掌握其使用。
动态 实现 SQL 2008行列转换的pivot
主要介绍了SQLServer行列互转实现思路,使用聚合函数pivot/unpivot实现行列互转,感兴趣的小伙伴们可以参考一下
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
带条件查询的参数化动态PIVOT行转列,方便快速完成行转列数据处理。
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
T-SQL语句中,Pivot运算符用于在列和行之间对数据进行旋转或透视转换,PIVOT命令可以实现数据表的列转行,同时执行聚合运算,UNPIVOT则与其相反,实现数据的行转列。
This intermediate course focuses on using SQL as a data query and manipulation tool. You learn to use the SQL procedure as a data retrieval tool within SAS programs. Specifically, you learn how to ...
Dynamic SQL: Applications, Performance, and Security in Microsoft SQL ...9.Dynamic Pivot and Unpivot 10.Solving Common Problems 11.Applications of Dynamic SQL 12.Index Usage and Maintenance Back Matter
主要介绍了sql server通过pivot对数据进行行列转换的方法,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
This book is an introduction and deep-dive into the many uses of dynamic SQL in Microsoft ...Chapter 9: Dynamic PIVOT and UNPIVOT Chapter 10: Solving Common Problems Chapter 11: Additional Applications
主要给大家介绍了关于行转列之SQL SERVER PIVOT与用法的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用SQL SERVER具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
sql 所有查询语句,如何使用全新的派生表 自连接 如何使用全新的pivot 如何使用全新的unpivot 如何使用全新的apply 如何使用全新的except 与intersect 运算符号 如何使用全新的“公用表达式” Compute[by]字句的使用...