首页 / MSSQL / sqlserver技术内幕&lt;二&gt;表运算符之pivot

sqlserver技术内幕&lt;二&gt;表运算符之pivot

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了sqlserver技术内幕&lt;二&gt;表运算符之pivot，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5549字，纯文字阅读大概需要8分钟。

内容图文

例一：在Sql Server的帮助文档中，对Pivot函数是这样解释的：可以使用 PIVOT 和 UNPIVOT 关系运算符对表表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一转换为输出中的多个列来转换表表达式，并在必要时对最终输出中所需的任何其余的列执

例一：

在Sql Server的帮助文档中，对Pivot函数是这样解释的：
可以使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合.

对第一次使用PIVOT函数的朋友来说，这样的解释很难让大家理解，下面编辑用PIVOT函数来实现一个行转列的功能，以便让读者更容易理解该函数.

注意：PIVOT是Sql Server2005的新函数，2005前行转列请参看本站：
SQLServer中(行列转换)行转列及列转行且加平均值及汇总值

先创建一个工资表:

Create Table Salary
(
HrName varchar(50),
Monthly varchar(50),
Money money
)

往表中插入数据：

insert into Salary(HrName,Monthly,[Money])
select '张三','一月','3000'
union all
select '张三','二月','3200'
union all
select '张三','三月','3500'
union all
select '李四','一月','3800'
union all
select '李四','二月','4200'
union all
select '李四','三月','3900'
union all
select '张三','一月','2000'

查看正常的数据：

select * from Salary

结果：

HrName Monthly Money
张三一月 3000.00
张三二月 3200.00
张三三月 3500.00
李四一月 3800.00
李四二月 4200.00
李四三月 3900.00
张三一月 2000.00

查看行转列后的数据：

select HrName as '姓名',[一月],[二月],[三月] from Salary
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) as pvt

结果：

姓名一月二月三月
李四 3800.00 4200.00 3900.00
张三 5000.00 3200.00 500.00

注意：
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) 中的sum([Money])，这里必须是聚合函数，比如是min,max等。
in ([一月],[二月],[三月])中的[一月],[二月],[三月]即为Monthly的Value,又为新结果集的列名.

如果我们将其中的一月改为四月，因为数据源中没有四月的记录，所以四月查询出来应该为Null.
测试：

select HrName as '姓名',[四月],[二月],[三月] from Salary
pivot(sum([Money]) for Monthly in ([四月],[二月],[三月])) as pvt

结果：

姓名四月二月三月
李四 NULL 4200.00 3900.00
张三 NULL 3200.00 3500.00

例二：

在SQLServer 2000环境中,如果要实现交叉表格报表,主要是靠一系列复杂的 SELECT...CASE 语句.

其实现过程请参阅这里T-SQL 交叉报表(行列互换) 交叉查询旋转查询

在SQLServer 2005中我们可以使用PIVOT关系运算符来实现行列转换.

还是以学生成绩表来举例:

id姓名科目成绩

1 张三语文 60
2 张三数学 65
3 张三外语 70
4 李四语文 80
5 李四数学 90
6 李四外语 85
7 王五语文 70
8 王五数学 71
9 王五外语 75
10 赵六语文 64
11 赵六数学 67
12 赵六外语 76

查询后得出:

姓名语文数学外语

李四 80 90 85
王五 70 71 75
张三 60 65 70
赵六 64 67 76

--准备数据:

select * from sysobjects where [xtype]='u'

if exists(select id from sysobjects where name='studentscore')

drop table studentscore--删除与实验冲突的表

create table studentscore--创建实验表

(

[id] int identity(1,1),

[name] nvarchar(20) not null,

subject nvarchar(20) not null,

score int not null

)

select * from studentscore

--添加实验数据

insert studentscore values ('张三','语文','60');

insert studentscore values ('张三','数学','65');

insert studentscore values ('张三','外语','70');

insert studentscore values ('李四','语文','80');

insert studentscore values ('李四','数学','90');

insert studentscore values ('李四','外语','85');

insert studentscore values ('王五','语文','70');

insert studentscore values ('王五','数学','71');

insert studentscore values ('王五','外语','75');

insert studentscore values ('赵六','语文','64');

insert studentscore values ('赵六','数学','67');

insert studentscore values ('赵六','外语','76');

select * from studentscore

sqlserver技术内幕<二>表运算符之pivot - 文章图片

使用 SELECT...CASE 语句实现代码如下

select [name],

语文=max(case

when subject='语文' then score else 0

end),

数学=max(case

when subject='数学' then score else 0

end),

外语=max(case

when subject='外语' then score else 0

end)

from studentscore

group by [name]

结果:

sqlserver技术内幕<二>表运算符之pivot - 文章图片

下面我们使用PIVOT关系运算符来实现行列转换

select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'

from (select score,subject,[name] from studentscore) as ss

pivot

(

sum(score) for subject in([语文],[数学],[外语])

) as pvt

结果:用较少的代码完成了交叉表格报表

sqlserver技术内幕<二>表运算符之pivot - 文章图片

============================

对于这种方法要注意的一点是,我们使用sum()聚合函数,表面上没有指定按什么方式分组,但是自动按照name列分组了.

怎么做到的呢?原来pivot关系运算符会根据前面的对象中的列来自行判断,在这个例子中pivot前面的对象是ss,是个子查询,这个子查询中只有三列,score,subject和[name],但是pivot运算符内部使用了score和subject这两列,那么肯定是对[name]分组.

所以我们得出,pivot运算符的分组规则是,跟随对象中的那些不在pivot运算符内部的列:

为了好理解我们再写一个例子:

--在ss这个子查询中,多加一列id

--那么pivot应该按照name和id进行分组

select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'

from (select score,subject,[name],id from studentscore) as ss

pivot

(

sum(score) for subject in([语文],[数学],[外语])

) as pvt

结果:验证了我们的设想

sqlserver技术内幕<二>表运算符之pivot - 文章图片

UNPIVOT关系运算符从字面上来看,就知道它的用途正好和PIVOT相反,下面举例说明:

if exists(select id from sysobjects where name='studentscore')

drop table studentscore--删除与实验冲突的表

create table studentscore--创建实验表

(

[id] int identity(1,1),

[name] nvarchar(20) not null,

yuwen int not null,

shuxue int not null,

waiyu int not null

)

select * from studentscore

--添加实验数据

insert studentscore values ('张三','60','65','70');

insert studentscore values ('李四','80','90','86');

insert studentscore values ('王五','70','71','75');

insert studentscore values ('赵六','64','67','76');

select * from studentscore

结果:

sqlserver技术内幕<二>表运算符之pivot - 文章图片

SELECT id, [name],subject, score

FROM

(SELECT id,[name], 语文=yuwen, 数学=shuxue, 外语=waiyu

FROM studentscore) as ss

UNPIVOT

(score FOR subject IN

(语文, 数学, 外语)

)AS unpvt

结果:

sqlserver技术内幕<二>表运算符之pivot - 文章图片

内容总结

以上是互联网集市为您收集整理的sqlserver技术内幕&lt;二&gt;表运算符之pivot全部内容，希望文章能够帮你解决sqlserver技术内幕&lt;二&gt;表运算符之pivot所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/532110.html

来源：【匿名】

【上一篇】sqlserver技术内幕<一>【下一篇】一个odbc连mssql分页的类

更多 ►

【sqlserver技术内幕&lt;二&gt;表运算符之pivot】教程文章相关的互联网学习教程文章

Sql Server中不常用的表运算符之APPLY(1)【代码】【图】

写在这个系列的前面:就像他们的名字一样，作为一个表运算，他们用来运算左表和右表。JOIN也是一个表运算符，不过他太常用了。 APPLY:将右表表达式应用在左表的每一行上。APPLY是Sql2005对SQL语句的拓展，这意味着其他类型的数据库可能不支持这个表运算符。微软设计APPLY的初衷在于将表值函数运用在左表的每一行，并将结果与对应行关联起来。网上大多数文章中提到的也是这样的用法，例如我们有一个Split(s,splitChar)表值函数，可以...

与MSSQL对比学习MYSQL的心得（五）--运算符【图】

安全等于运算符（<=>）这个操作符和=操作符执行相同的比较操作，不过<=>可以用来判断NULL值。在两个操作数均为NULL时，其返回值为1而不为NULL；而当一个操作数为NULL时，其返回值为0而不为NULL。下面分别是 SELECT NULL <=>1 SELECT 1<=>0 SELECT NULL <=>NULL 的执行结果在两个操作数均为NULL时，其返回值为1而不为NULL；LEAST运算符语法格式为：LEAST（值1,值2,...值n），其中值n表示参数列表中有n个值。在有两个或多个...

SQL Server里PIVOT运算符的”红颜祸水“【代码】

概述 SQL Server里PIVOT运算符背后的基本思想是在T-SQL查询期间，你可以旋转行为列。运算符本身是SQL Server 2005后引入的，主要用在基于建立在实体属性值模型（Entity Attribute Value model (EAV)）原则上的数据库。EAM模型背后的想法是你可以扩展数据库实体，而不需要进行数据库架构的修改。因此EAV模型存储实体的所有属性以键/值对存储在一个表里。我们来看下面一个简单的键/值对模型的表。 CREATE TABLE EAVTable (RecordID I...

浅谈SQL Server 之 PIVOT运算符用法

相信大家在处理数据库编程时，也许会常用到pivot运算符。今天把我近段时间处理的一个简单报表用到pivot运算符与大家分享一下。比如,针对一个职员基础表tb_Employee(ID,EmpID,EmpName,DptNo,DptDesc,InDate,...),利用pivot分析职工流动率情况。在入职日期不确定的情况下，如何动态处理所要的结果为题。即，如何实现： SELECT DptNo,Dpt,[2000] AS Y2000,[2001] AS Y2001,[2002] AS Y2002,...,[2017] AS Y2017 FROM (SELECT DptNo,...

SQL Server调优系列基础篇（联合运算符总结）【代码】【图】

--新建表 SELECT EmployeeID,FirstName+N‘ ‘+LastName AS ContactName,City,Country INTO NewEmployees FROM Employees GO --添加索引 ALTER TABLE NewEmployees ADD CONSTRAINT PK_NewEmployees PRIMARY KEY(EmployeeID) CREATE INDEX ContactName ON NewEmployees(ContactName) CREATE INDEX ContactName ON CUSTOMERS(ContactName) GO --新建查询，这里一定要加上一个显示的Order by才能出现合并连接去重 SELECT ContactName ...

SQL Server调优系列基础篇（常用运算符总结——三种物理连接方式剖析）【代码】【图】

SELECT E1.EmployeeID,ECNT.CNT FROM Employees E1 CROSS APPLY (SELECT COUNT(*) CNTFROM Employees E2WHERE E1.HireDate<E2.HireDate )ECNT 通过上述代码查询的结果项，和上面的是一样的，只是我们根据外部表的结果对内部表进行了过滤，这样执行的时候就不需要获取全部数据项了。我们查看下文本执行计划我们比较一下，前后两条语句的执行消耗，对比一下执行效率执行时间从1秒179毫秒减少至93毫秒。效果明显。对比CPU消耗、...

sqlserver技术内幕&lt;二&gt;表运算符之pivot

例一：在Sql Server的帮助文档中，对Pivot函数是这样解释的：可以使用 PIVOT 和 UNPIVOT 关系运算符对表表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一转换为输出中的多个列来转换表表达式，并在必要时对最终输出中所需的任何其余的列执例一：在Sql Server的帮助文档中，对Pivot函数是这样解释的：可以使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯...

sqlserver技术内幕&lt;二&gt;表运算符之apply

SQL SERVER 2005新增了APPLY运算符。该运算符有些类于表连接（JOIN)，但是功能却有不同。APPLY的右表表达式可以引用左输入中的列。在JOIN运算的时候，连接的2个表之间没有优先级，但是使用APPLY时，就像先计算左表输入，然后为左输入中的每一行计算一次右输 SQL SERVER 2005新增了APPLY运算符。该运算符有些类似于表连接（JOIN)，但是功能却有不同。APPLY的右表表达式可以引用左输入中的列。在JOIN运算的时候，连接的2个表之间没有...

SQLServerAPPLY表运算符使用介绍

SQL Server 2005（含）以上版本，新增了APPLY表运算，为我们日常查询带来了极大的方便。新增的APPLY表运算符把右表表达式应用到左表表达式中的每一行。它不像JOIN那样先计算那个表表达式都可以，APPLY必选先逻辑地计算左表达式。这种计算输入的逻辑顺序允许吧右表达式关联到左表表达式。 APPLY有两种形式，一个是OUTER APPLY，一个是CROSS APPLY，区别在于指定OUTER，意味着结果集中将包含使右表表达式为空的左表表达式中的行，而指...

SQLSERVER中union,cube,rollup,cumpute运算符使用说明

union,cube,rollup,cumpute运算符的使用技巧。/* --1 UNION 运算符是将两个或更多查询的结果组合为单个结果集使用 UNION 组合查询的结果集有两个最基本的规则： 1。所有查询中的列数和列的顺序必须相同。 2。数据类型必须兼容 a.UNION的结果集列名与第一个select语句中的结果集中的列名相同，其他select语句的结果集列名被忽略 b.默认情况下，UNION 运算符是从结果集中删除重复行。如果使用all关键字，那么结果集将包含所有行并且...

被遗忘的SQLServer比较运算符谓词

SQLServer中有三个关键字可以修改比较运算符：All、Any和Some，其中Some和Any等价。官方的参考文档 http://technet.microsoft.com/zh-cn/library/ms187074%28SQL.90%29.aspx 他们作用于比较运算符和子查询之间，作用类似Exists、not exists、in、not in以及其他逻辑意义，这些语法同样被SQLServer2000支持但是很少看到有人用它们。代码如下:set nocount on use tempdb go if (object_id (t1 ) is not null ) drop table t1 creat...

SQLSERVER对索引的利用及非SARG运算符认识

SQL对筛选条件简称：SARG（search argument/SARG）当然这里不是说SQLSERVER的where子句，是说SQLSERVER对索引的利用,感兴趣的朋友可以了解下，或许本文的知识点对你有所帮助哈写SQL语句的时候很多时候会用到filter筛选掉一些记录，SQL对筛选条件简称：SARG（search argument/SARG）代码如下:where amount>4000 and amount<6000上面这句就是筛选条件当然这里不是说SQLSERVER的where子句，是说SQLSERVER对索引的利用在SQLSERVER对...

SQLServer2000中的并行处理和执行计划中的位图运算符【图】

SQL Server 2000中的并行处理和执行计划中的位图运算符摘抄自：SQLServer 2000并行处理和位图简介刘志斌并行查询介绍 Degree of Parallelism（并行度）一个查询使用并行来处理时，SQL Server为该查询分配多个线程，每个线程使用一个CPU进行操作。 DegreeSQL Server 2000中的并行处理和执行计划中的位图运算符摘抄自：SQLServer 2000并行处理和位图简介刘志斌并行查询介绍Degree of Parallelism（并行度）一个查询使用并行来...

被遗忘的SQLServer比较运算符修饰词

SQLServer中有三个关键字可以修改比较运算符：All、Any和Some，其中Some和Any等价。官方的参考文档 %28SQL.90%29.aspx 他们作用于比较运算符和子查询之间，作用类似Exists、not exists、in、not in以及其他逻辑意义，这些语法同样被SQLServer2000支持但是很SQLServer中有三个关键字可以修改比较运算符：All、Any和Some，，其中Some和Any等价。官方的参考文档 %28SQL.90%29.aspx 他们作用于比较运算符和子查询之间，作用类似Exis...

与MSSQL对比学习MYSQL的心得（五）--运算符_MySQL【图】

安全等于运算符（）这个操作符和=操作符执行相同的比较操作，不过<=>可以用来判断NULL值。在两个操作数均为NULL时，其返回值为1而不为NULL；而当一个操作数为NULL时，其返回值为0而不为NULL。下面分别是 SELECT NULL <=>1 SELECT 1<=>0 SELECT NULL <=>NULL 的执行结果在两个操作数均为NULL时，其返回值为1而不为NULL；LEAST运算符语法格式为：LEAST（值1,值2,...值n），其中值n表示参数列表中有n个值。在有两个或多个参数的情况...

首页 / MSSQL / sqlserver技术内幕&amp;lt;二&amp;gt;表运算符之pivot

sqlserver技术内幕&amp;lt;二&amp;gt;表运算符之pivot

内容导读

内容图文

内容总结

内容备注

内容手机端

【sqlserver技术内幕&amp;lt;二&amp;gt;表运算符之pivot】教程文章相关的互联网学习教程文章

SQLSERVER - 相关标签

技术 - 相关标签

MSSQL - 最新教程

MSSQL - 最热教程

首页 / MSSQL / sqlserver技术内幕<二>表运算符之pivot

sqlserver技术内幕<二>表运算符之pivot

【sqlserver技术内幕<二>表运算符之pivot】教程文章相关的互联网学习教程文章