`
javababy1
  • 浏览: 1225678 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

[学习SQL SERVER 2005系列]感受新功能一:PIVOT

阅读更多
  1. [学习SQLSERVER2005系列]感受新功能一:PIVOT
  2. 工具的升级,我以为得先看看这个工具在哪些功能上得到加强,今天我们就看看SQL2005这个PIVOT吧。PIVOT关系运算符对表值表达式进行操作以获得另一个表。PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式,并在必要时对最终输出中所需的任何其余的列值执行聚合。记得我们在SQL2000中要用聚合和CASE语句完成一个行列转换吧,特别当待转成列的数据不定时,我们往往构造动态SQL,然后用EXEC来运行。
  3. 环境准备:
  4. ------------------------------------
  5. --Author:happyflsytone
  6. --Version:V1.001
  7. --Date:2008-09-1810:20:53
  8. ------------------------------------
  9. --TestData:ta
  10. IFOBJECT_ID('ta')ISNOTNULL
  11. DROPTABLEta
  12. ;
  13. CREATETABLEta(idINT,col1Nvarchar(2),col2Nvarchar(2),col3Nvarchar(4),col4INT)
  14. ;
  15. INSERTINTOta
  16. SELECT1,'HN','CS','abc',1UNIONALL
  17. SELECT2,'HN','CS','abcd',2UNIONALL
  18. SELECT3,'HN','CD','abcd',3UNIONALL
  19. SELECT4,'HN','HY','ae',4
  20. ;
  21. 我们先来回顾SQL2000的行列转换,比如我们对上例程把col3转列显示,并把col4的和当对应列值。我们分两种情况来讨论:
  22. 一、当col3的列值固定就是'abc','abcd','ae'三种情况
  23. SELECT
  24. col1,
  25. col2,
  26. [abc]=SUM(CASEWHENcol3='abc'THENcol4ELSE0END),
  27. [abcd]=SUM(CASEWHENcol3='abcd'THENcol4ELSE0END),
  28. [ae]=SUM(CASEWHENcol3='ae'THENcol4ELSE0END)
  29. FROMta
  30. GROUPBYcol1,col2
  31. /*
  32. col1col2abcabcdae
  33. -----------------------------------------
  34. HNCD030
  35. HNCS120
  36. HNHY004
  37. (3行受影响)
  38. */
  39. 二、当col3的列值不固定时就运用动态SQL,其实也就是构造一个sum(CASEWHEN...)SQL字符串
  40. DECLARE@svarchar(8000)
  41. SELECT@s=isnull(@s+',
  42. ','')+'['+col3+']=SUM(CASEWHENcol3='''+col3+'''THENcol4ELSE0END)'
  43. FROM(SELECTdistinctcol3FROMta)a
  44. SET@s='SELECT
  45. col1,
  46. col2,
  47. '+@s+'
  48. FROMta
  49. GROUPBY
  50. col1,col2'
  51. EXEC(@s)
  52. /*
  53. col1col2abcabcdae
  54. -----------------------------------------
  55. HNCD030
  56. HNCS120
  57. HNHY004
  58. (3行受影响)
  59. */
  60. 我们先输入这个@S看看是什么东东,只要加上print@s
  61. SELECT
  62. col1,
  63. col2,
  64. [abc]=SUM(CASEWHENcol3='abc'THENcol4ELSE0END),
  65. [abcd]=SUM(CASEWHENcol3='abcd'THENcol4ELSE0END),
  66. [ae]=SUM(CASEWHENcol3='ae'THENcol4ELSE0END)
  67. FROMta
  68. GROUPBY
  69. col1,col2
  70. 其实就是上面我们构造的固定列值的SQL嘛。
  71. 好,现在们开始在2005中实现这个功能,先来看看2005的FROM子句的定义(关于如何看这个定义请参照SQL2005的文档约定及Transate-SQL语法约定):
  72. [FROM{<table_source>}[,...n]]
  73. <table_source>::=
  74. {
  75. <pivoted_table>
  76. }
  77. <pivoted_table>::=
  78. table_sourcePIVOT<pivot_clause>table_alias
  79. <pivot_clause>::=
  80. (aggregate_function(value_column)
  81. FORpivot_column
  82. IN(<column_list>)
  83. )
  84. <column_list>::=
  85. column_name[,...]
  86. pivot_column和value_column是PIVOT运算符使用的组合列。PIVOT遵循以下过程获得输出结果集:
  87. 对分组列的input_table执行GROUPBY,为每个组生成一个输出行。
  88. 输出行中的分组列获得input_table中该组的对应列值。
  89. 通过执行以下操作,为每个输出行生成列列表中的列的值:
  90. 针对pivot_column,对上一步在GROUPBY中生成的行另外进行分组。
  91. 对于column_list中的每个输出列,选择满足以下条件的子组:
  92. pivot_column=CONVERT(<datatypeofpivot_column>,'output_column')
  93. 针对此子组上的aggregate_function对value_column求值,其结果作为相应的output_column的值返回。如果该子组为空,SQLServer将为该output_column生成空值。如果聚合函数是COUNT,且子组为空,则返回零(0)。
  94. 接着我们利用我们开头的例子来理解一下这个FROM子句,很显然我们的col4对应上面的value_column,我们还假定列会下固定为这三项,那么列col3对应上面的pivot_column,进而我们应该得出[abc],[abcd],[ae]是column_name即我们的输出列,最后我们只要构造一下table_source就可以了,如何构造这个table_source,显然pivot_column和value_column应该包含在其中,其它就应该是你想要分组的列啦.
  95. 我们来总结一下:这个FROM子句是基于table_source对pivot_column进行透视,table_source中pivot_column和value_column列之外的列被称为透视运算符的组合列,而PIVOT是对输入表执行组合列的分组操作,并为每个组返回一行,好,我们试着写出这个SQL:
  96. SELECTcol1,col2,[abc],[abcd],[ae]
  97. FROM
  98. (SELECTcol1,col2,col3,col4
  99. FROMta)p
  100. PIVOT
  101. (SUM(col4)
  102. FORcol3IN([abc],[abcd],[ae])
  103. )ASunpvt
  104. 我们执行一下看看结果:
  105. /*
  106. col1abcabcdae
  107. -------------------------------------
  108. HN1NULLNULL
  109. HNNULL2NULL
  110. HNNULL3NULL
  111. HNNULLNULL4
  112. (4行受影响)
  113. */
  114. 如果我们去掉这些NULL那么可以这样:
  115. SELECTcol1,col2,ISNULL([abc],0)AS[ABC],ISNULL([abcd],0)AS[ABCD],ISNULL([ae],0)AS[AE]
  116. FROM
  117. (SELECTcol1,col2,col3,col4
  118. FROMta)p
  119. PIVOT
  120. (SUM(col4)
  121. FORcol3IN([abc],[abcd],[ae])
  122. )ASunpvt
  123. /*
  124. col1col2ABCABCDAE
  125. -----------------------------------------
  126. HNCD030
  127. HNCS120
  128. HNHY004
  129. (3行受影响)
  130. */
  131. 当然在2005中列值不固定时也要用到动态SQL,我们把这个例子完成如下:
  132. DECLARE@sVARCHAR(1000)
  133. SELECT@s=isnull(@s+',','')+'['+ltrim(COL3)+']'
  134. FROM(SELECTDISTINCTcol3FROMta)a
  135. EXEC('SELECTcol1,col2,'+@s+'
  136. FROM
  137. (SELECTcol1,col2,COL3,COL4
  138. FROMTA)p
  139. PIVOT
  140. (SUM(COL4)
  141. FORCOL3IN('+@s+')
  142. )ASunpvt')
  143. /*
  144. col1col2abcabcdae
  145. -----------------------------------------
  146. HNCDNULL3NULL
  147. HNCS12NULL
  148. HNHYNULLNULL4
  149. (3行受影响)
  150. */
  151. 最后我们再完成一个table_source是多表关联的例子,准备数据如下:
  152. --TestData:ta
  153. Ifobject_id('ta')isnotnull
  154. Droptableta
  155. ;
  156. Createtableta(idint,省nvarchar(2),市nvarchar(2),具体货品nvarchar(4))
  157. ;
  158. Insertintota
  159. select1,'HN','CS','abc'unionall
  160. select2,'HN','CS','abcd'unionall
  161. select3,'HN','CD','abcd'unionall
  162. select4,'HN','HY','ae'
  163. ;
  164. --TestData:tb
  165. Ifobject_id('tb')isnotnull
  166. Droptabletb
  167. ;
  168. Createtabletb(编号int,具体货品nvarchar(5),大类别int)
  169. ;
  170. Insertintotb
  171. select1,'abc',1unionall
  172. select2,'abcd',2unionall
  173. select3,'abcde',1unionall
  174. select4,'ae',3
  175. Go
  176. --Start
  177. -----2005写法
  178. select@s=isnull(@s+',','')+'['+ltrim(大类别)+']'
  179. from(selectdistincttop100percent大类别fromtborderby大类别)a
  180. exec('SELECT省,市,'+@s+'
  181. FROM
  182. (SELECT省,市,大类别,a.编号
  183. FROMtaaleftjointbbona.具体货品=b.具体货品)p
  184. PIVOT
  185. (COUNT(编号)
  186. FOR大类别IN('+@s+')
  187. )ASunpvt')
  188. --Result:
  189. /*
  190. (3行受影响)
  191. 省市123
  192. -----------------------------------------
  193. HNCD010
  194. HNCS110
  195. HNHY001
  196. (3行受影响)
  197. */
  198. --End
  199. 好,我们对2005的PIVOT这个新功能的学习就到这儿了,多练习就可以熟练的使用这个PIVOT用法。
分享到:
评论

相关推荐

    SQL Server 2005新功能之PIVOT的描述

    在SQL Server 2005中,PIVOT是一个重要的新特性,它使得数据的行列转换更为便捷。在SQL Server 2000及其以前版本,如果需要将数据的某列值转换为行对应的列,通常需要使用复杂的CASE语句配合聚合函数(如SUM)来实现...

    SQL Server 2005 Beta 2 Transact-SQL 增强功能

    SQL Server 2005 Beta 2带来了对Transact-SQL的显著增强,这些改进主要集中在...对于熟悉SQL Server 2000的用户来说,掌握这些新特性将有助于充分利用SQL Server 2005的新功能,提高数据库管理和应用程序开发的水平。

    SQL2005查询工具 SQLServer2005_SSMSEE

    描述中提到的"简单安装SQL软件可以下载使用",意味着SQL Server 2005 SSMSEE的安装过程相对简单,用户只需下载`SQLServer2005_SSMSEE.msi`这个安装文件,按照向导指示即可完成安装。安装后,用户无需复杂的配置就能...

    SQL Server 2005两个十个最重要的特点

    SQL Server 2005 引入了一系列新的 T-SQL 特性,包括 PIVOT、APPLY、ROW_NUMBER 等,这些新特性显著增强了 SQL Server 的数据处理能力,使复杂的查询变得更加简单。 ### 11. 通知服务 SQL Server 2005 提供了通知...

    SQL Server 2005 T-SQL增强

    在SQL Server 2005中,T-SQL得到了显著增强,其中一个重要方面是引入了新的数据类型。这些数据类型更加丰富且灵活,能够更好地支持现代应用程序的需求。例如: - **日期与时间**: SQL Server 2005引入了`DATE`, `...

    Sql Server 2014 安装包

    SQL Server Express是SQL Server的一个免费、轻量级版本,适合个人学习、小型企业和开发项目使用。虽然功能相比完整版有所限制,但仍然提供核心的数据库服务,如关系数据存储、事务处理、备份恢复以及基本的查询和...

    SQL Server 2005 BI综合案例系列课程(1):财务管理常规分析建模

    在本课程"SQL Server 2005 BI综合案例系列课程(1):财务管理常规分析建模"中,我们将深入探讨如何使用SQL Server 2005的商业智能(BI)功能来构建一个针对财务管理的高效分析模型。该课程特别适合IT专业人员、数据库...

    SQL Server 2005深入内幕——开发人员,最大的赢家:SQL Server 2005全新的开发特性.pdf

    SQL Server 2005对T-SQL的功能进行了多方面的改进,如优化了TOP子句,引入了CTE(公共表表达式),添加了PIVOT和UNPIVOT操作符,以及新的DDL触发器和TRY-CATCH错误处理结构。 例如,书中可能包含了使用CLR创建存储...

    SQL Server 2005 BI综合案例系列课程(6):销售管理中的智能化前端展现

    这是一系列课程的第六部分,旨在通过具体的案例来帮助学习者理解如何利用SQL Server 2005的BI功能提升数据分析和决策支持能力。 首先,我们要了解SQL Server 2005 BI的核心组件,包括SQL Server Integration ...

    SQL Server 全系列安装教程.zip_2012安装SQL2000_SQL SERVER 2008_SQL Server

    4. **SQL Server 2012 安装**:此版本进一步加强了BI功能,如Power Pivot、Power View等,还引入了AlwaysOn高可用性解决方案。安装时,要关注新的BI工具的配置,以及AlwaysOn的环境准备和配置,包括复制、故障转移...

    SQL Server 2008开发新特性系列课程(15):SQL Server 2008报表服务新特性

    SQL Server 2008是微软推出的一款强大的关系型数据库管理系统,它在前任...提供的"Session15 SQL Server 2008 RS New Features.pdf"文件,将详细阐述这些特性,是学习和掌握SQL Server 2008报表服务新功能的重要资源。

    SQL Server 2008开发新特性系列课程(3):SQL Server 2008数据管理概述

    **SQL Server 2008开发新特性系列课程(3): SQL Server 2008数据管理概述** 在本课程中,我们将深入探讨SQL Server 2008在数据管理方面的创新特性,这些特性极大地提升了数据库的性能、可用性和安全性。SQL Server ...

    Transact—SQL在SQL Server 2005中的增强功能.pdf

    在SQL Server 2005版本中,Transact-SQL(T-SQL)语言经历了重要的增强,引入了一系列的新功能和改进,这些更新极大地提升了数据库的处理能力、查询效率和错误处理机制。我们将详细解读这些增强功能,并给出相应的...

    SQL SERVER 2008安装包.rar

    SQL Server 2008是微软推出的一款关系型数据库管理系统,它在企业级数据管理和分析领域扮演着重要的角色。此安装包提供了64位和32位两种版本,以适应不同硬件环境的需求。以下是关于SQL Server 2008的一些核心知识点...

    SQL Server 2008开发新特性系列课程(14):SQL Server 2008分析服务可管理性

    在本课程中,我们将深入探讨SQL Server 2008中的一个关键组件——分析服务(Analysis Services)的新特性。SQL Server 2008作为微软的一款强大数据库管理系统,其分析服务部分在数据仓库和商业智能领域扮演着至关...

    培训SQL Server 2005

    SQL Server 2005是Microsoft推出的一款关系型数据库管理系统,它在数据库管理、数据存储、数据处理等方面具有丰富的功能。对于SQL Server 2005的学习和培训,以下是一些核心知识点: 1. **数据类型增强**: - **...

    SQL SERVER 2005+ 版本行列转换数据脚本

    ### SQL Server 2005+ 版本行列转换数据...通过上述分析,我们可以了解到`dduser`表的基本结构及其数据插入方式,并且掌握了如何利用SQL Server的`PIVOT`功能来进行行列转换处理。这对于后续的数据分析工作非常有帮助。

    SQL SERVER 2008新功能介绍

    相较于之前的SQL Server 2005,SQL Server 2008引入了一系列的新功能和技术改进,旨在帮助企业更加高效、安全地处理数据。 #### 二、SQL Server 2008 数据平台愿景 微软的数据平台愿景是为了应对信息存储爆炸所...

    SQL Server 2005大型数据仓库系统设计经验分享

    #### 一、SQL Server 2005 数据仓库增强功能 SQL Server 2005 在数据仓库方面进行了大量的增强,主要包括以下几个方面: - **关系型数据仓库**:SQL Server 2005 支持构建高性能的关系型数据仓库,能够处理大量...

    Microsoft SQL Server 2008有何新功能(英文)

    主服务器和镜像服务器之间是实时的,以确保数据的一致性。在SQL Server 2008中,引入了日志流压缩技术,通过压缩日志传输的数据量,...这些新特性使得SQL Server 2008成为了当时最全面和最先进的数据库管理系统之一。

Global site tag (gtag.js) - Google Analytics