本文为原创,如需转载,请注明作者和出处,谢谢!
在输出统计结果时可能需要将列变成行,而将聚合结果(如count、sum)作为记录的第一行,先看如下的SQL语句:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->declare@ttable(namevarchar(20))
insert@t
select'abc'unionall
select'xxx'unionall
select'xxx'unionall
select'ttt'
select*from@t
在执行上面的SQL语句后,会输出如图1所示的记录集。
图1
上图显示的是一个普通的记录集,如果要统计name字段的每个值的重复数,需要进行分组,如下面的SQL如示:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->selectcount(name)asc,namefrom@tgroupbyname
执行上面的SQL语句后的查询结果如图2所示。
图2
如果我们有一个需求,需要如图3所示的聚合结果。
图3
从图3可以看出,查询结果正好是图2的结果逆时针旋转90度,也就是说,name列的值变成了列名,而c列的值变成了第一行的记录。图2所示的c和name字段消失了。
当然,要达到这个结果并不困难,看如下的SQL语句:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->select(selectcount(name)from@twherename='abc')asabc,
(selectcount(name)from@twherename='ttt')asxxx,
(selectcount(name)from@twherename='xxx')asttt
上
面的SQL语句会出输出如图3的查询结果。但这里有个问题,上面的SQL语句是枚举了name列所有可能的值,在本例中只有三个值('abc',
'ttt','xxx'),这非常好枚举,但如果有很多值,SQL语句会变得非常长,非常不利于编写。当然,可以通过编程的方式自动生成,但最终结果仍然
会生成很长的SQL语句。
为了解决这个问题,在SQL Server2005中提供了一个pivot函数,该函数可以很容易地输出如图3所示的记录集,如下面的SQL语句所示:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->select*from@tpivot(count(name)fornamein([abc],[ttt],[xxx]))
在执行上面的SQL语句同样可以获得图3所示的查询结果。实际上,pivot函数也起到了分组的作用。在使用pivot函数时应注意如下几点:
1.pivot函数需要指定聚合函数,如count、sum等,for关键字和聚合函数都要使用需要聚合的字段名,在本例中是name。
2.in关键字负责指定每组需要聚合的值,用[...]将这些值括起来。实际上,这些值也相当于我们第一种聚合方法中的where条件,例如,where name='abc'、where name='ttt',当然,这些值也是输出记录集的列名。
3.在最后要为pivot函数起一个别名。
虽然当要聚合的值很多时(或不确定),也需要动态生成SQL语句,但使用pivot函数的SQL语句却短很多。
如
果我们还有一个需求,要将图3的结果变成图2的结果,也就是顺时针旋转90度,仍然以c和name作为字段名。也许方法很多,但SQL
Server2005提供了一个unpivot函数,该函数是pivot函数的逆过程。也就是将记录集顺时针旋转90度,先看下面的SQL语句:
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->declare@ttable(namevarchar(20))
insert@t
select'abc'unionall
select'xxx'unionall
select'xxx'unionall
select'ttt'
;
withttas(
select*from@tpivot(count(name)fornamein([abc],[ttt],[xxx]))p)
select*fromtt
上面的SQL语句将输出如图3所示的结果。如果将最后一条SQL语句(select * from tt)换成如下的SQL语句,将输出如图2所示的结果。
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->select*fromttunpivot([c]fornamein([abc],[xxx],[ttt]))p
要注意的是,[c]中的c表示聚合结果列的字段名,name表示要聚合列的字段名,这两个值可以是任意满足字段名命名规则的字符串,[abc] ,[xxx],[ttt]分别是图3所示的记录集的字段名,这些值必须一致。执行下面的SQL语句将获得图4的输出结果。
<!--<br />
<br />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br />
http://www.CodeHighlighter.com/<br />
<br />
-->select*fromttunpivot([统计值]for统计名in([abc],[xxx],[ttt]))p
图4
分享到:
相关推荐
SQL Server2005杂谈(5):将聚合记录集逆时针和顺时针旋转90度.在输出统计结果时可能需要将列变成行,而将聚合结果(如count、sum)作为记录的第一行
SQL Server 2005 杂谈 聚合 记录集 逆时针 顺时针 旋转 90度 doc
Microsoft SQL Server技术内幕丛书的历史 丛书结构 《Microsoft SQL Server 2005技术内幕:T-SQL查询》 《Microsoft SQL Server 2005技术内幕:T-SQL...《Microsoft SQL Server 2005技术内幕:查询、调整和优化》
Microsoft SQL Server 2005技术内幕:T-SQL查询的源代码,主要是SQL脚本
Microsoft SQL Server 2005技术内幕:T-SQL查询.part1
该书解释并比较了SQL Server 2000和SQL Server 2005在数据库开发相关问题上的解决方案,深入讨论了SQL Server 2005中新增的T-SQL编程特性,包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解...
Microsoft SQL Server技术内幕丛书的历史 丛书结构 《Microsoft SQL Server 2005技术内幕:T-SQL查询》 《Microsoft SQL Server 2005技术内幕:T-SQL...《Microsoft SQL Server 2005技术内幕:查询、调整和优化》
Microsoft SQL Server 2005技术内幕:T-SQL查询 pdf 中文版 第二部分 第一部分地址:http://download.csdn.net/source/2684220
——《Microsoft SQL Server 2005 技术内幕:T-SQL程序设计》、《Microsoft SQL Server 2005 技术内幕:T-SQL查询》、《Microsoft SQL Server 2005 技术内幕:查询、调整和优化》、《Microsoft SQL Server 2005 技术...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
Microsoft SQL Server 2005技术内幕:T-SQL查询,经典书籍,上传受限,所以分开两个文件
《Microsoft SQL Server 2005技术内幕:T-SQL查询》示例代码
SQL Server 2005微软官方权威参考书. 公球公认SQL Server 2005 经典著作.. 数据库“铁人”、微软MVP胡百敬先生鼎力推荐 微软SQL Server 总部Principal Group 项目经理朱凌志鼎力推荐 本书详细介绍了数据...
SQL Server 2005 杂谈 在SQL Server2005中按列 连接字符串 三种方法 doc SQL Server 2005 杂谈 在SQL Server2005中按列 连接字符串 三种方法 doc
学习sql server 和sql 的两本经典的著作: 《sql server 2005 技术内幕 T-SQL查询》 《sql server 2005 技术内幕 T-SQL程序设计》 网上大多的资源都是英文的,好容易找到中文的了,上传上来和大家分享。 这两本书都...
——《Microsoft SQL Server 2005 技术内幕:T-SQL程序设计》、《Microsoft SQL Server 2005 技术内幕:T-SQL查询》、《Microsoft SQL Server 2005 技术内幕:查询、调整和优化》、《Microsoft SQL Server 2005 技术...
以SQL Server顶尖专家的视角,带你深入到SQL Server 2005性能调优和优化的内部。该书包括指导性强的实践、实用的建议及丰富的示例代码,使你的查询语句效率更高,效果更好,以达到数据库性能的优化。 探索如何 通过...
SQL Server 2005 杂谈 使用 公用表表达式 CTE 简化 嵌套 SQL SQL Server 2005 杂谈 使用 公用表表达式 CTE 简化 嵌套 SQL
Microsoft SQL Server 2005技术内幕:T-SQL查询 pdf 中文版 1 第一部分 第二部分地址:http://download.csdn.net/source/2684248