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

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