sql server 中的行列转换

 

最近在研究行列转换。在2000版本中，一律用case 语句来完成，但是在2005中新增了pivot运算符，它让你能够将行级数据旋转成为列表数据，实现类似Excel数据透视表的功能，而不需要像先前版本的SQLServer一样使用CASE语句。

 

以下是从别人的博客上抄下来的，很有参考价值：

 

Sql代码 复制代码
  1. /*   
  2. 标题：普通行列转换(version 2.0)   
  3. 作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)   
  4. 时间：2008-03-09   
  5. 地点：广东深圳   
  6. 说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。   
  7.   
  8. 问题：假设有张学生成绩表(tb)如下:   
  9. 姓名 课程 分数   
  10. 张三 语文 74   
  11. 张三 数学 83   
  12. 张三 物理 93   
  13. 李四 语文 74   
  14. 李四 数学 84   
  15. 李四 物理 94   
  16. 想变成(得到如下结果)：    
  17. 姓名 语文 数学 物理    
  18. ---- ---- ---- ----   
  19. 李四 74   84   94   
  20. 张三 74   83   93   
  21. -------------------   
  22. */   
  23.   
  24. create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)   
  25. insert into tb values('张三' , '语文' , 74)   
  26. insert into tb values('张三' , '数学' , 83)   
  27. insert into tb values('张三' , '物理' , 93)   
  28. insert into tb values('李四' , '语文' , 74)   
  29. insert into tb values('李四' , '数学' , 84)   
  30. insert into tb values('李四' , '物理' , 94)   
  31. go   
  32.   
  33. --SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)   
  34. select 姓名 as 姓名 ,   
  35.   max(case 课程 when '语文' then 分数 else 0 end) 语文,   
  36.   max(case 课程 when '数学' then 分数 else 0 end) 数学,   
  37.   max(case 课程 when '物理' then 分数 else 0 end) 物理   
  38. from tb   
  39. group by 姓名   
  40.   
  41. --SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)   
  42. declare @sql varchar(8000)   
  43. set @sql = 'select 姓名 '  
  44. select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'  
  45. from (select distinct 课程 from tb) as a   
  46. set @sql = @sql + ' from tb group by 姓名'  
  47. exec(@sql)    
  48.   
  49. --SQL SERVER 2005 静态SQL。   
  50. select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b   
  51.   
  52. --SQL SERVER 2005 动态SQL。   
  53. declare @sql varchar(8000)   
  54. select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程   
  55. set @sql = '[' + @sql + ']'  
  56. exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')   
  57.   
  58. ---------------------------------   
  59.   
  60. /*   
  61. 问题：在上述结果的基础上加平均分，总分，得到如下结果：   
  62. 姓名 语文 数学 物理 平均分 总分    
  63. ---- ---- ---- ---- ------ ----   
  64. 李四 74   84   94   84.00  252   
  65. 张三 74   83   93   83.33  250   
  66. */   
  67.   
  68. --SQL SERVER 2000 静态SQL。   
  69. select 姓名 姓名,   
  70.   max(case 课程 when '语文' then 分数 else 0 end) 语文,   
  71.   max(case 课程 when '数学' then 分数 else 0 end) 数学,   
  72.   max(case 课程 when '物理' then 分数 else 0 end) 物理,   
  73.   cast(avg(分数*1.0) as decimal(18,2)) 平均分,   
  74.   sum(分数) 总分   
  75. from tb   
  76. group by 姓名   
  77.   
  78. --SQL SERVER 2000 动态SQL。   
  79. declare @sql varchar(8000)   
  80. set @sql = 'select 姓名 '  
  81. select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'  
  82. from (select distinct 课程 from tb) as a   
  83. set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'  
  84. exec(@sql)    
  85.   
  86. --SQL SERVER 2005 静态SQL。   
  87. select m.* , n.平均分 , n.总分 from  
  88. (select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,   
  89. (select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n   
  90. where m.姓名 = n.姓名   
  91.   
  92. --SQL SERVER 2005 动态SQL。   
  93. declare @sql varchar(8000)   
  94. select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程   
  95. exec ('select m.* , n.平均分 , n.总分 from  
  96. (select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,    
  97. (select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n   
  98. where m.姓名 = n.姓名')   
  99.   
  100. drop table tb       
  101.   
  102. ------------------   
  103. ------------------   
  104.   
  105. /*   
  106. 问题：如果上述两表互相换一下：即表结构和数据为：   
  107. 姓名 语文 数学 物理   
  108. 张三 74　　83　　93   
  109. 李四 74　　84　　94   
  110. 想变成(得到如下结果)：    
  111. 姓名 课程 分数    
  112. ---- ---- ----   
  113. 李四 语文 74   
  114. 李四 数学 84   
  115. 李四 物理 94   
  116. 张三 语文 74   
  117. 张三 数学 83   
  118. 张三 物理 93   
  119. --------------   
  120. */   
  121.   
  122. create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)   
  123. insert into tb values('张三',74,83,93)   
  124. insert into tb values('李四',74,84,94)   
  125. go   
  126.   
  127. --SQL SERVER 2000 静态SQL。   
  128. select * from  
  129. (   
  130.  select 姓名 , 课程 = '语文' , 分数 = 语文 from tb    
  131.  union all  
  132.  select 姓名 , 课程 = '数学' , 分数 = 数学 from tb   
  133.  union all  
  134.  select 姓名 , 课程 = '物理' , 分数 = 物理 from tb   
  135. ) t   
  136. order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end  
  137.   
  138. --SQL SERVER 2000 动态SQL。   
  139. --调用系统表动态生态。   
  140. declare @sql varchar(8000)   
  141. select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'  
  142. from syscolumns    
  143. where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列   
  144. order by colid asc  
  145. exec(@sql + ' order by 姓名 ')   
  146.   
  147. --SQL SERVER 2005 动态SQL。   
  148. select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t   
  149.   
  150. --SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。   
  151.   
  152. --------------------   
  153. /*   
  154. 问题：在上述的结果上加个平均分，总分，得到如下结果：   
  155. 姓名 课程   分数   
  156. ---- ------ ------   
  157. 李四 语文   74.00   
  158. 李四 数学   84.00   
  159. 李四 物理   94.00   
  160. 李四 平均分 84.00   
  161. 李四 总分   252.00   
  162. 张三 语文   74.00   
  163. 张三 数学   83.00   
  164. 张三 物理   93.00   
  165. 张三 平均分 83.33   
  166. 张三 总分   250.00   
  167. ------------------   
  168. */   
  169.   
  170. select * from  
  171. (   
  172.  select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb    
  173.  union all  
  174.  select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb   
  175.  union all  
  176.  select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb   
  177.  union all  
  178.  select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb   
  179.  union all  
  180.  select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb   
  181. ) t   
  182. order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end  
  183. drop table tb  

 

 

语法规则
<pivot_clause> ::=
        ( aggregate_function ( value_column )
        FOR pivot_column
        IN ( <column_list> )
    )
<unpivot_clause> ::=
        ( value_column FOR pivot_column IN ( <column_list> ) )

table_source PIVOT <pivot_clause>
指定基于 table_source 对 pivot_column 进行透视。table_source 是表或表表达式。输出是包含 table_source 中 pivot_column 和 value_column 列之外的所有列的表。table_source 中 pivot_column 和 value_column 列之外的列被称为透视运算符的组合列。
PIVOT 对输入表执行组合列的分组操作，并为每个组返回一行。此外，input_table 的 pivot_column 中显示的 column_list 中指定的每个值，输出中都对应一列。
有关详细信息，请参阅“备注”部分和使用 PIVOT 和 UNPIVOT。
aggregate_function
系统或用户定义的聚合函数。聚合函数应该对空值固定不变。对空值固定不变的聚合函数在求聚合值时不考虑组中的空值。
不允许使用 COUNT(*) 系统聚合函数。
value_column
PIVOT 运算符的值列。与 UNPIVOT 一起使用时，value_column 不能是输入 table_source 中的现有列的名称。
FOR pivot_column
PIVOT 运算符的透视列。pivot_column 必须属于可隐式或显式转换为 nvarchar() 的类型。此列不能为 image 或 rowversion。
使用 UNPIVOT 时，pivot_column 是从 table_source 中提取的输出列的名称。table_source 中不能有该名称的现有列。
IN ( column_list )
在 PIVOT 子句中，列出 pivot_column 中将成为输出表的列名的值。该列表不能指定被透视的输入 table_source 中已存在的任何列名。
在 UNPIVOT 子句中，列出 table_source 中将被提取到单个 pivot_column 中的列。

 

在这里有两点需要说明：

1.在语法规则的table_source处要写成select * from xxx，用*而不是具列名。反正我在测试的时候用*能通过，用具体列名无法通过。

2.在语法规则的<column_list>处，要写成常量，也就是具体的字符串，如：(北京，上海，武汉)，而不能写成select语句。

以上是大概的了解，到了具体项目中再来具体研究吧。

参考的文章：

Pivot Table 实现详解（一）

http://www.cnblogs.com/Chinasf/archive/2008/04/13/1151368.html

SQL Server 2005 中行列转换(Pivot 和 UNPivot的使用)

http://www.cnblogs.com/talantlee/articles/561745.html

PIVOT 在 SQL Server 2005/2008 上的应用

http://www.cnblogs.com/Nettech/articles/1340214.html

普通行列转换

http://blog.csdn.net/chenjianyong94/archive/2009/05/06/4155420.aspx

posted @ 2010-07-22 11:33  你妹的sb  阅读(433)  评论(0)  编辑  收藏  举报
百度一下