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

1. [学习SQL SERVER 2005系列]感受新功能一：PIVOT
3.     工具的升级，我以为得先看看这个工具在哪些功能上得到加强，今天我们就看看SQL2005这个PIVOT吧。PIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。记得我们在SQL2000中要用聚合和CASE语句完成一个行列转换吧，特别当待转成列的数据不定时，我们往往构造动态SQL，然后用EXEC来运行。
4.     环境准备：
5. ------------------------------------
6. -- Author:  happyflsytone  
7. -- Version:V1.001  
8. -- Date:2008-09-18 10:20:53
9. ------------------------------------
11. -- Test Data: ta
12. IF OBJECT_ID('ta') IS NOT NULL 
13.     DROP TABLE ta
14. ;
15. CREATE  TABLE ta(id INT,col1 Nvarchar(2),col2 Nvarchar(2),col3 Nvarchar(4),col4 INT)
16. ;
17. INSERT INTO ta
18. SELECT 1,'HN','CS','abc',1 UNION ALL
19. SELECT 2,'HN','CS','abcd',2 UNION ALL
20. SELECT 3,'HN','CD','abcd' ,3UNION ALL
21. SELECT 4,'HN','HY','ae' ,4
22. ;
24.     我们先来回顾SQL2000的行列转换,比如我们对上例程把col3转列显示，并把col4的和当对应列值。我们分两种情况来讨论：
25.     一、当col3的列值固定就是'abc','abcd','ae'三种情况
26. SELECT 
27.     col1,
28.     col2,
29.     [abc]   = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),
30.     [abcd]  = SUM(CASE WHEN col3 = 'abcd' THEN col4  ELSE 0 END),
31.     [ae]    = SUM(CASE WHEN col3 = 'ae' THEN col4  ELSE 0 END)
32. FROM ta
33. GROUP BY col1,col2
34. /*
35. col1 col2 abc         abcd        ae
36. ---- ---- ----------- ----------- -----------
37. HN   CD   0           3           0
38. HN   CS   1           2           0
39. HN   HY   0           0           4
41. (3 行受影响)
42. */
43.     二、当col3的列值不固定时就运用动态SQL，其实也就是构造一个sum(CASE WHEN ...)SQL字符串
44. DECLARE @s varchar(8000)
45. SELECT @s = isnull(@s+',
46.     ','') +'['+col3+'] = SUM(CASE WHEN col3 = '''+col3+''' THEN col4 ELSE 0 END)'
47. FROM ( SELECT distinct col3 FROM ta) a
48. SET @s = 'SELECT 
49.     col1,
50.     col2,
51.     '+@s + ' 
52. FROM ta 
53. GROUP BY 
54.     col1,col2'
55. EXEC(@s)
56. /*
57. col1 col2 abc         abcd        ae
58. ---- ---- ----------- ----------- -----------
59. HN   CD   0           3           0
60. HN   CS   1           2           0
61. HN   HY   0           0           4
63. (3 行受影响)
64. */
65.     我们先输入这个@S看看是什么东东，只要加上print @s
66.     
67. SELECT 
68.     col1,
69.     col2,
70.     [abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),
71.     [abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),
72.     [ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END) 
73. FROM ta 
74. GROUP BY 
75.     col1,col2
76.     
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_source PIVOT <pivot_clause> table_alias
87. <pivot_clause> ::=
88.         ( aggregate_function ( value_column ) 
89.         FOR pivot_column 
90.         IN ( <column_list> ) 
91.     ) 
92. <column_list> ::=
93.           column_name [ , ... ]
95. pivot_column 和 value_column 是 PIVOT 运算符使用的组合列。PIVOT 遵循以下过程获得输出结果集：
96. 对分组列的 input_table 执行 GROUP BY，为每个组生成一个输出行。
97. 输出行中的分组列获得 input_table 中该组的对应列值。
98. 通过执行以下操作，为每个输出行生成列列表中的列的值：
99. 针对 pivot_column，对上一步在 GROUP BY 中生成的行另外进行分组。
100. 对于 column_list 中的每个输出列，选择满足以下条件的子组：
101. pivot_column = CONVERT(<data type of pivot_column>, 'output_column')
102. 针对此子组上的 aggregate_function 对 value_column 求值，其结果作为相应的 output_column 的值返回。如果该子组为空，SQL Server 将为该 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：
105.     
106. SELECT col1,col2,[abc],[abcd],[ae]
107. FROM 
108.    (SELECT col1,col2,col3,col4
109.     FROM ta ) p
110. PIVOT
111.    ( SUM (col4) 
112.     FOR col3 IN ([abc],[abcd],[ae])
113.    )AS unpvt
114.    
115. 我们执行一下看看结果：
116. /*
117. col1 abc         abcd        ae
118. ---- ----------- ----------- -----------
119. HN   1           NULL        NULL
120. HN   NULL        2           NULL
121. HN   NULL        3           NULL
122. HN   NULL        NULL        4
124. (4 行受影响)
125. */
126. 如果我们去掉这些NULL那么可以这样：
127. SELECT col1,col2,ISNULL([abc],0) AS [ABC],ISNULL([abcd],0) AS [ABCD],ISNULL([ae],0) AS [AE]
128. FROM 
129.    (SELECT col1,col2,col3,col4
130.     FROM ta ) p
131. PIVOT
132.    ( SUM (col4) 
133.     FOR col3 IN ([abc],[abcd],[ae])
134.    )AS unpvt
137. /*
139. col1 col2 ABC         ABCD        AE
140. ---- ---- ----------- ----------- -----------
141. HN   CD   0           3           0
142. HN   CS   1           2           0
143. HN   HY   0           0           4
145. (3 行受影响)
146. */
147. 当然在2005中列值不固定时也要用到动态SQL，我们把这个例子完成如下：
148. DECLARE @s VARCHAR(1000)
149. SELECT @s = isnull(@s + ',','')+ '['+ltrim(COL3)+']'
150. FROM (SELECT DISTINCT col3 FROM ta ) a
152. EXEC('SELECT col1,col2,'+@s+'
153. FROM 
154.    (SELECT col1,col2,COL3,COL4
155.    FROM TA) p
156. PIVOT
157.    ( SUM (COL4) 
158.     FOR COL3 IN ('+@s+')
159.    )AS unpvt')
160. /*
161. col1 col2 abc         abcd        ae
162. ---- ---- ----------- ----------- -----------
163. HN   CD   NULL        3           NULL
164. HN   CS   1           2           NULL
165. HN   HY   NULL        NULL        4
167. (3 行受影响)
168. */
169. 最后我们再完成一个table_source是多表关联的例子，准备数据如下：
170. -- Test Data: ta
171. If object_id('ta') is not null 
172.     Drop table ta
173. ;
175. Create table ta(id int,省 nvarchar(2),市 nvarchar(2),具体货品 nvarchar(4))
176. ;
177. Insert into ta
178. select 1,'HN','CS','abc' union all
179. select 2,'HN','CS','abcd' union all
180. select 3,'HN','CD','abcd' union all
181. select 4,'HN','HY','ae' 
182. ;
183. -- Test Data: tb
184. If object_id('tb') is not null 
185.     Drop table tb
186. ;
187. Create table tb(编号 int,具体货品 nvarchar(5),大类别 int)
188. ;
189. Insert into tb
190. select 1,'abc',1 union all
191. select 2,'abcd',2 union all
192. select 3,'abcde',1 union all
193. select 4,'ae',3 
194. Go
195. --Start
197. -----2005写法
198. select @s = isnull(@s + ',','')+ '['+ltrim(大类别)+']'
199. from (select distinct top 100 percent  大类别 from tb order by 大类别 ) a
201. exec('SELECT 省,市,'+@s+'
202. FROM 
203.    (SELECT 省,市,大类别,a.编号
204.    FROM ta a left join tb b on a.具体货品 = b.具体货品) p
205. PIVOT
206.    ( COUNT (编号) 
207.     FOR 大类别 IN ('+@s+')
208.    )AS unpvt')
211. --Result:
212. /*
215. (3 行受影响)
217. 省    市    1           2           3
218. ---- ---- ----------- ----------- -----------
219. HN   CD   0           1           0
220. HN   CS   1           1           0
221. HN   HY   0           0           1
223. (3 行受影响)
225. */
226. --End 
228. 好，我们对2005的PIVOT这个新功能的学习就到这儿了，多练习就可以熟练的使用这个PIVOT用法。