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

[学习SQL SERVER 2005系列]感受新功能二:UNPIVOT

阅读更多
  1. [学习SQLSERVER2005系列]感受新功能二:UNPIVOT
  2. 今晚我们就看看SQL2005这个UNPIVOT吧。UNPIVOT几乎完全是PIVOT相反的操作,将列转换为行。它和PIVOT关系运算符一样对表值表达式进行操作以获得另一个表。记得我们在SQL2000中要用UNIONALL来把多列合并到一列的情况吧,同样对于列不定时,我们往往还利用系统表syscolumns来构造动态SQL,然后用EXEC来运行。
  3. 环境准备:
  4. ------------------------------------
  5. --Author:happyflsytone
  6. --Date:2008-09-2214:05:26
  7. ------------------------------------
  8. --TestData:ta
  9. IFOBJECT_ID('ta')ISNOTNULL
  10. DROPTABLEta
  11. Go
  12. CREATETABLEta(col1nvarchar(2),col2nvarchar(2),Anvarchar(1),Bnvarchar(1),Cnvarchar(1))
  13. Go
  14. INSERTINTOta
  15. select'HN','CD','0','3','0'unionall
  16. select'HN','CS','1','2','0'unionall
  17. select'HN','HY','0','0','4'
  18. GO
  19. 我们先来回顾SQL2000的行列转换,比如我们对上例程把abcg列转行显示。我们分两种情况来讨论:
  20. 一、当col3的列值固定就是A、B、C三列的情况
  21. SELECTCOL1,COL2,NEWCOL='A',A
  22. FROMTA
  23. UNIONALL
  24. SELECTCOL1,COL2,NEWCOL='B',B
  25. FROMTA
  26. UNIONALL
  27. SELECTCOL1,COL2,NEWCOL='C',C
  28. FROMTA
  29. ORDERBYCOL1,COL2,NEWCOL
  30. /*
  31. COL1COL2NEWCOLA
  32. -------------------------
  33. HNCDA0
  34. HNCDB3
  35. HNCDC0
  36. HNCSA1
  37. HNCSB2
  38. HNCSC0
  39. HNHYA0
  40. HNHYB0
  41. HNHYC4
  42. */
  43. 二、当除COL1COL2外的列很多时如果我们还一个一个写unionall就会很累了,这时我们往往读系统表构造SQL串,其实也就是构造一个select....unionallselect....SQL字符串
  44. DECLARE@SVARCHAR(8000)
  45. SELECT@S=ISNULL(@S+'
  46. UNIONALL','')+'
  47. SELECTCOL1,COL2,NEWCOL='''+NAME+''','+NAME+'ASNEWCOLVFROMTA'
  48. FROMSYS.COLUMNSWHEREOBJECT_ID=OBJECT_ID('TA')ANDNAMENOTIN('COL1','COL2')
  49. PRINT(@S+'
  50. ORDERBYCOL1,COL2,NEWCOL')
  51. /*
  52. COL1COL2NEWCOLNEWCOLV
  53. -------------------------
  54. HNCDA0
  55. HNCDB3
  56. HNCDC0
  57. HNCSA1
  58. HNCSB2
  59. HNCSC0
  60. HNHYA0
  61. HNHYB0
  62. HNHYC4
  63. */
  64. 我们先输入这个@S看看是什么东东,只要加上print@s
  65. SELECTCOL1,COL2,NEWCOL='A',AASNEWCOLVFROMTA
  66. UNIONALL
  67. SELECTCOL1,COL2,NEWCOL='B',BASNEWCOLVFROMTA
  68. UNIONALL
  69. SELECTCOL1,COL2,NEWCOL='C',CASNEWCOLVFROMTA
  70. ORDERBYCOL1,COL2,NEWCOL
  71. 其实就是上面我们构造的固定列值的SQL嘛。
  72. 好,现在们开始在2005中实现这个功能,先来看看2005的FROM子句的定义(关于如何看这个定义请参照SQL2005的文档约定及Transate-SQL语法约定):
  73. [FROM{<table_source>}[,...n]]
  74. <table_source>::=
  75. {
  76. <unpivoted_table>
  77. }
  78. <unpivoted_table>::=
  79. table_sourceUNPIVOT<unpivot_clause>table_alias
  80. <unpivot_clause>::=
  81. (value_columnFORpivot_columnIN(<column_list>))
  82. <column_list>::=
  83. column_name[,...]
  84. pivot_column和value_column是UNPIVOT运算符使用的组合列。指定输入表从column_list中的多个列缩减为名为pivot_column的单个列。
  85. 注意了,我们这儿默认你ABC列的类型是一致的。
  86. 接着我们利用我们开头的例子来理解一下这个FROM子句,很显然我们的[NEWCOLV]对应上面的value_column,我们还假定列会下固定为这三项,那么列[NEWCOL]对应上面的pivot_column,进而我们应该得出[a],[b],[c]是column_name即我们要合并的列,最后我们只要构造一下table_source就可以了,如何构造这个table_source,显然pivot_column和value_column新生成的列,其它就应该是你想要分组的列啦.
  87. 我们来总结一下:这个FROM子句是基于table_source对pivot_column进行透视,table_source中pivot_column和value_column列之外的列被称为透视运算符的组合列,而UNPIVOT是对输入表执行列的合并操作,并为每个单列返回一新行(二列包含当前列的列名及列值),好,我们试着写出这个SQL:
  88. SELECTcol1,col2,[NEWCOL],[NEWCOLV]
  89. FROM
  90. (SELECTcol1,col2,A,B,C
  91. FROMta)p
  92. UNPIVOT
  93. (NEWCOLV
  94. FORNEWCOLIN(A,B,C)
  95. )ASunpvt
  96. 我们执行一下看看结果:(为了使输出好看,我对newcol做了处理,只要把[NEWCOL]改写成CAST([NEWCOL]ASVARCHAR(2))AS[NEWCOL]即可,至于什么差别大家一试就知道。)
  97. /*
  98. col1col2NEWCOLNEWCOLV
  99. ---------------------
  100. HNCDA0
  101. HNCDB3
  102. HNCDC0
  103. HNCSA1
  104. HNCSB2
  105. HNCSC0
  106. HNHYA0
  107. HNHYB0
  108. HNHYC4
  109. (9行受影响)
  110. */
  111. 当然在2005中列值不固定时也要用到动态SQL,我们把这个例子完成如下:
  112. DECLARE@sVARCHAR(1000)
  113. SELECT@s=isnull(@s+',','')+'['+ltrim(NAME)+']'
  114. FROMSYS.COLUMNSWHEREOBJECT_ID=OBJECT_ID('TA')ANDNAMENOTIN('COL1','COL2')
  115. ;
  116. EXEC('SELECTcol1,col2,NEWCOL,NEWCOLV
  117. FROM
  118. (SELECTcol1,col2,'+@s+'
  119. FROMTA)p
  120. UNPIVOT
  121. (NEWCOLV
  122. FORNEWCOLIN('+@s+')
  123. )ASunpvt')
  124. /*
  125. col1col2NEWCOLNEWCOLV
  126. -----------------------------------------------------------------------------------------------------------------------------------------------
  127. HNCDA0
  128. HNCDB3
  129. HNCDC0
  130. HNCSA1
  131. HNCSB2
  132. HNCSC4
  133. HNHYA0
  134. HNHYB0
  135. HNHYC4
  136. (9行受影响)
  137. */
  138. 下面我们对特殊情况做点补充,顺便一起来复习一下PIVOT操作。当我们上面的例程中的ABC三列出NULL时,结果会什么样呢,首先们利用PIVOT来生成我所说的这种带有NULL示例数据:
  139. ------------------------------------
  140. --Author:happyflsytone
  141. --Date:2008-09-2214:33:20
  142. ------------------------------------
  143. --TestData:ta
  144. IFOBJECT_ID('ta')ISNOTNULL
  145. DROPTABLEta
  146. Go
  147. CREATETABLEta(col1nvarchar(2),col2nvarchar(2),NEWCOLnvarchar(1),NEWCOLVint)
  148. Go
  149. INSERTINTOta
  150. select'HN','CD','A','0'unionall
  151. select'HN','CD','B',nullunionall
  152. select'HN','CD','C','0'unionall
  153. select'HN','CS','A','1'unionall
  154. select'HN','CS','B','2'unionall
  155. select'HN','CS','C',nullunionall
  156. select'HN','HY','A','0'unionall
  157. select'HN','HY','B','0'unionall
  158. select'HN','HY','C','4'
  159. GO
  160. --Start
  161. SELECTcol1,col2,[A],[B],[C]
  162. FROM
  163. (SELECTCOL1,COL2,newcolv,newcol
  164. FROMTA)P
  165. PIVOT
  166. (SUM(NEWCOLV)
  167. FORNEWCOLIN([A],[B],[C])
  168. )ASunpvt
  169. --Result:
  170. /*
  171. col1col2ABC
  172. -----------------------------------------
  173. HNCD0NULL0
  174. HNCS12NULL
  175. HNHY004
  176. (3行受影响)
  177. */
  178. --End
  179. 我们看上面的B和C列都有我们所说的NULL出现了,好我们先通过UNPIVOT来把行列转换一下看看结果:
  180. ------------------------------------
  181. --Author:happyflsytone
  182. --Date:2008-09-2214:05:26
  183. ------------------------------------
  184. --TestData:ta
  185. IFOBJECT_ID('ta')ISNOTNULL
  186. DROPTABLEta
  187. Go
  188. CREATETABLEta(col1nvarchar(2),col2nvarchar(2),Anvarchar(1),Bnvarchar(1),Cnvarchar(1))
  189. Go
  190. INSERTINTOta
  191. select'HN','CD','0',null,'0'unionall
  192. select'HN','CS','1','2',nullunionall
  193. select'HN','HY','0','0','4'
  194. GO
  195. SELECTcol1,col2,CAST([NEWCOL]ASVARCHAR(2))AS[NEWCOL],[NEWCOLV]
  196. FROM
  197. (SELECTcol1,col2,A,B,C
  198. FROMta)p
  199. UNPIVOT
  200. (NEWCOLV
  201. FORNEWCOLIN(A,B,C)
  202. )ASunpvt
  203. /*
  204. col1col2NEWCOLNEWCOLV
  205. ---------------------
  206. HNCDA0
  207. HNCDC0
  208. HNCSA1
  209. HNCSB2
  210. HNHYA0
  211. HNHYB0
  212. HNHYC4
  213. (7行受影响)
  214. */
  215. 很显然转换后的最终结果和我们一起的相比发现少了两行,这两行就是一开始列值有NULL的记录,这就是UNPIVOT的一个特殊的地方:UNPIVOT的输入中的NULL不会显示在输出中。大家一定要注意这一点。
  216. 最后,我们对前一讲的PIVOT和现在这个UNPIVOT进行一个总结,我们说UNPIVOT几乎是PIVOT的的反操作,并不完全是PIVOT的逆操作,为什么说不完全是?刚才上面这个先PIVOT再UNPIVOT后的记录忽略了NULL的情况首先就能说明不完全是反操作,下面我们再通过另一个例程说这个不完全:
  217. ------------------------------------
  218. --Author:happyflsytone
  219. --Date:2008-09-2214:33:20
  220. ------------------------------------
  221. --TestData:ta
  222. IFOBJECT_ID('ta')ISNOTNULL
  223. DROPTABLEta
  224. Go
  225. CREATETABLEta(col1nvarchar(2),col2nvarchar(2),NEWCOLnvarchar(1),NEWCOLVint)
  226. Go
  227. INSERTINTOta
  228. select'HN','CD','A',0unionall
  229. select'HN','CD','B',2unionall
  230. select'HN','CD','C',0unionall
  231. select'HN','CD','C',5unionall
  232. select'HN','CS','A',1unionall
  233. select'HN','CS','B',2unionall
  234. select'HN','CS','B',2unionall
  235. select'HN','CS','C',4unionall
  236. select'HN','HY','A',0unionall
  237. select'HN','HY','A',9unionall
  238. select'HN','HY','B',0unionall
  239. select'HN','HY','C',4
  240. GO
  241. --Start
  242. PRINT'--------------原始数据----------------------------'
  243. SELECT*
  244. FROMTA
  245. SELECTcol1,col2,[A],[B],[C]
  246. FROM
  247. (SELECTCOL1,COL2,newcolv,newcol
  248. FROMTA)P
  249. PIVOT
  250. (SUM(NEWCOLV)
  251. FORNEWCOLIN([A],[B],[C])
  252. )ASunpvt
  253. --Result:
  254. /*
  255. col1col2ABC
  256. -----------------------------------------
  257. HNCD025
  258. HNCS144
  259. HNHY904
  260. (3行受影响)
  261. */
  262. --End
  263. 我们接着把上面的结果作为原始数据进行列转成行,
  264. --TestData:ta
  265. IFOBJECT_ID('ta')ISNOTNULL
  266. DROPTABLEta
  267. Go
  268. CREATETABLEta(col1NVARCHAR(2),col2NVARCHAR(2),AINT,BINT,CINT)
  269. Go
  270. INSERTINTOta
  271. SELECT'HN','CD','0',2,5UNIONALL
  272. SELECT'HN','CS','1',4,4UNIONALL
  273. SELECT'HN','HY','9',0,4
  274. GO
  275. --Start
  276. PRINT'--------------还原的原始数据----------------------------'
  277. SELECTcol1,col2,CAST([NEWCOL]ASVARCHAR(2))AS[NEWCOL],[NEWCOLV]
  278. FROM
  279. (SELECTcol1,col2,A,B,C
  280. FROMta)p
  281. UNPIVOT
  282. (NEWCOLV
  283. FORNEWCOLIN(A,B,C)
  284. )ASunpvt
  285. /*
  286. --------------原始数据----------------------------
  287. col1col2NEWCOLNEWCOLV
  288. -------------------------
  289. HNCDA0
  290. HNCDB2
  291. HNCDC0
  292. HNCDC5
  293. HNCSA1
  294. HNCSB2
  295. HNCSB2
  296. HNCSC4
  297. HNHYA0
  298. HNHYA9
  299. HNHYB0
  300. HNHYC4
  301. (12行受影响)
  302. --------------还原的原始数据----------------------------
  303. col1col2NEWCOLNEWCOLV
  304. -------------------------
  305. HNCDA0
  306. HNCDB2
  307. HNCDC5
  308. HNCSA1
  309. HNCSB4
  310. HNCSC4
  311. HNHYA9
  312. HNHYB0
  313. HNHYC4
  314. (9行受影响)
  315. */
  316. 通过原始数据及还原的原始数据对比,我们发现UNPIVT是无法反操作PIVOT操作时运用聚合函数生成的新列的情况,所以我们小结如下:
  317. 1、如果PIVOT中使用聚合函数,则计算聚合时将不考虑出现在值列中的任何NULL值;
  318. 2、UNPIVOT的输入中的NULL不会显示在输出中;
  319. 3、UNPIVOT的输出会无法完全还原PIVOT操作之前输入中可能会含有原始的NULL值;
  320. 4、UNPIVOT的输出会无法完全还原PIVOT操作之前输入中各行的明细值(因为用了聚合函数);
  321. 好,我们对2005的UNPIVOT及PIVOT这个新功能的学习就到这儿了。
  322. 无枪狙击手
  323. 于宁.一个风雨交加的夜晚
分享到:
评论

相关推荐

    SQL2005查询工具 SQLServer2005_SSMSEE

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

    SQL Server 2005 T-SQL增强

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

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

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

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

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

    SQL知识点之列转行Unpivot函数

    在SQLSERVER中,Unpivot函数的语法如下: ```sql SELECT CustomerID, Phone, PhoneNumber FROM dbo.CustomerPhones UNPIVOT ( PhoneNumber FOR Phone IN (Phone1, Phone2, Phone3) ) AS Unpvt; ``` 在这个查询中...

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

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

    培训SQL Server 2005

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

    SQLServer行列互转实现思路(聚合函数)

    总的来说,SQL Server的Pivot和Unpivot函数提供了强大的行转列和列转行功能,极大地简化了数据处理过程,使得复杂的数据分析变得更为便捷。通过熟练掌握这两个函数,可以提高我们在处理大数据时的效率和灵活性。对于...

    Microsoft SQL Server 2008技术内幕:T-SQL查询(英文版)

    ### Microsoft SQL Server 2008技术内幕:T-SQL查询 #### 书籍概述 《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨Microsoft SQL Server 2008中T-SQL查询及其性能优化的专业书籍。本书由Lubor ...

    SQL Server2005 T-SQL 概述

    SQL Server 2005版本对T-SQL进行了显著的增强,引入了如PIVOT、UNPIVOT、APPLY以及OVER子句等新特性,这些增强功能极大地丰富了数据处理的能力和灵活性。然而,本文主要聚焦于T-SQL的核心组成部分——逻辑查询处理...

    Microsoft SQL Server 2008技术内幕

    《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威书籍。T-SQL是SQL Server的主要编程语言,用于执行数据操作、管理数据库以及创建复杂的存储过程...

    sql2005培训资料

    **SQL2005培训资料概述** SQL Server 2005是微软公司推出的一款强大的关系型数据库管理系统,它在数据库管理、数据...通过学习这些资料,用户将能够更好地理解和应用SQL Server 2005,提高数据库管理和开发的效率。

    在Sql Server 数据库中利用存储过程实现动态交叉表

    在数据库管理与开发领域,SQL Server作为一个广泛应用的数据库系统,提供了强大的存储过程功能,允许开发者在数据库层面执行复杂的逻辑操作。存储过程是存储在SQL Server中的预编译的SQL代码块,它可以通过调用执行...

    数据库SQL2005简装版.rar

    1. **Transact-SQL(T-SQL)增强**:SQL Server 2005对T-SQL进行了大量优化,引入了新的编程元素,如CTE(公共表表达式)、窗口函数(ROW_NUMBER(), RANK(), DENSE_RANK()等)以及Pivot和Unpivot操作,使得复杂查询...

    T-SQl查询总结.doc

    SQL Server 2005作为微软的一款重要数据库管理系统,引入了许多新特性和增强功能,尤其是其T-SQL(Transact-SQL)语言,使得数据库管理和数据操作更为高效。本文将对SQL Server 2005中的T-SQL查询进行详尽的总结,...

    sql学习的 学习心得

    - **INSERT 和 UPDATE 优化**:SQL Server 2005 引入的新特性支持对 INSERT 和 UPDATE 操作进行优化,特别是在 OLTP 和 OLAP 场景下,能够实现更高效的并发处理。 #### 五、其他重要特性 - **CLR 集成**:CLR ...

    SQL Sever DBA必备的数据库基础知识

    SQL Server DBA(数据库管理员)在维护和管理数据库系统时,需要掌握一系列核心的知识点,以确保系统的稳定运行和数据的安全性。以下是针对给定文件中提到的SQL Server DBA必备的数据库基础知识的详细说明: 1. SQL...

    SQLServer2019中的十个最重要的T-SQL增强功能-图文.pptx

    SQL Server 2019 引入了许多重要的T-SQL增强功能,这些更新极大地提高了数据库管理、查询效率和数据处理能力。...通过学习和熟练掌握这些新特性,专业人士可以更好地利用SQL Server 2019来优化他们的数据库解决方案。

    SQL2005数据仓库设计经验

    - **SQL Server 2005**在数据仓库方面引入了一系列新特性,包括更好的性能优化、更强大的数据分析能力和更灵活的报表服务。 - **表分区与索引分区**:为了提高大规模数据集的查询性能,SQL Server 2005支持表分区...

Global site tag (gtag.js) - Google Analytics