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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值