单表行转列

单表行转列 2008-08-08 13:45 把表 id sid          course result 1 2005001 语文      80.0      2 2005001 数学      90.0      3 2005001 英语      80.0      4 2005002 语文      56.0      5 2005002 数学      69.0 要将其转变为： 可以看得出来，表2列是由表1的行数据转化而来的，只有姓名列是表1中的列。 一步一步的来嘛，其他的列难搞，如果表2只有1列姓名的话，那么very easy了，不就是一个简单的分组嘛。 Select 姓名 From 成绩表 Group By 姓名 现在sql语句的架子搭起来了，无论以后如何变化，分组是少不了的。 那么开始下一步，想这个结果集中再添加1列，多了我们不加，因为你不论是能处理语文，还是数学，还是英语列，那么其他的列只要原样照抄就可以了，顶多修改一下参数而已。 从语文列开始吧，这一列的数据都可以从表1中找到，如果我们只要“语文”这一列，那么也好写。 Sql语句疑似： Select 姓名,case when 课程='语文' then 成绩 end From 成绩表 Group By 姓名 那就F5吧。 嗯，啊，...挂了。 赶紧看错误提示： 服务器: 消息 8120，级别 16，状态 1，行 1 列 '成绩表.成绩' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。 服务器: 消息 8120，级别 16，状态 1，行 1 列 '成绩表.课程' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。 字段成绩和课程还必须在聚合函数中或Group By中，如果在Group By中呢？ 数据倒是有了，可是行多了点，看来只能从聚合入手了。 到底是那个聚合？聚合有max,min,avg,sum,count，max和min首先干掉，我们不是要最大值，最小值，难道是平均值？难道是个数？难道是求和？？？ 那就试试呗。 Select 姓名,sum(case when 课程='语文' then 成绩 end) From 成绩表 Group By 姓名 嘿，真不错。 该有的都有了，就是没列名，好办，给个别名嘛。 Select 姓名,sum(case when 课程='语文' then 成绩 end) As '语文' From 成绩表 Group By 姓名 那么其他几列也就好办了。 充分发扬CVS精神。 Select 姓名, sum(case when 课程='语文' then 成绩 end) As '语文' , sum(case when 课程='数学' then 成绩 end) As '数学', sum(case when 课程='英语' then 成绩 end) As '英语' From 成绩表 Group By 姓名 可是结果有NULL，那还不好办，你的case when then end没写完，还有else没写呢！ Select 姓名, sum(case when 课程='语文' then 成绩 else 0 end) As '语文' , sum(case when 课程='数学' then 成绩 else 0 end) As '数学', sum(case when 课程='英语' then 成绩 else 0 end) As '英语' From 成绩表 Group By 姓名 终于修成正果了。 真的？ 当然是假的？ 怎么假？ 这不废话么！你现在写的Sql语句只能支持科目表中只有语文数学英语，那万一课程中加了一个叫物理的，那你不得改程序了吗？！ 怎么办？凉拌。 也就是说这个Sql语句中有变化的地方在红字部分： Select 姓名, sum(case when 课程='语文' then 成绩 else 0 end) As '语文' , sum(case when 课程='数学' then 成绩 else 0 end) As '数学', sum(case when 课程='英语' then 成绩 else 0 end) As '英语' From 成绩表 Group By 姓名 红字部分要从哪里来？如果专心看，那么应该能从中间看出来，哪些个“As '语文'”，哪些个“课程='语文'”，其实是数据表中的值，如果仅仅要查询“语文，数学，英语”的话，那么easy啊。 Select 课程 From 成绩表 Group By 课程 可是我们要的结果是： sum(case when 课程='语文' then 成绩 else 0 end) As '语文' , sum(case when 课程='数学' then 成绩 else 0 end) As '数学', sum(case when 课程='英语' then 成绩 else 0 end) As '英语' 那不简单，就是些字符串嘛。 我拼，我拼拼拼。 Select ',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+'''' From 成绩表 Group By 课程 结果为： 哈哈，只要将这一段和刚才的Select 姓名拼在一起就可以了。 现在麻烦来了，拼没有问题。 declare @sql nvarchar(4000) set @sql='Select 姓名 ' Select @sql=@sql+',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+'''' From 成绩表 Group By 课程 Print @sql 这一段的结果是： Select 姓名 ,sum(case when 课程='数学' then 成绩 else 0 end) As '数学',sum(case when 课程='英语' then 成绩 else 0 end) As '英语',sum(case when 课程='语文' then 成绩 else 0 end) As '语文' 跟我们的要求比较接近了，还要附加一点东西： Select 姓名 ,sum(case when 课程='数学' then 成绩 else 0 end) As '数学',sum(case when 课程='英语' then 成绩 else 0 end) As '英语',sum(case when 课程='语文' then 成绩 else 0 end) As '语文' From 成绩表 Group By 姓名 现在麻烦的是，如何执行这个字符串，现在我们将sql语句放在了 @sql 这个变量中了，怎么执行？ 灵机两动！ SqlServer中有个专门将字符串作为sql语句执行的存储过程---- sp_executesql。 就是它了。 最终版本： declare @sql nvarchar(4000) set @sql='Select 姓名 ' Select @sql=@sql+',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+'''' From 成绩表 Group By 课程 Print @sql set @sql=@sql+' From 成绩表 Group By 姓名' Print @sql execute sp_executesql @sql 当然课程信息，不一定会从成绩表中获取，很有可能是一个专门的表格，比如叫“课程表”，那么就更easy了。 declare @sql nvarchar(4000) set @sql='Select 姓名 ' Select @sql=@sql+',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+'''' From 课程表 Print @sql set @sql=@sql+' From 成绩表 Group By 姓名' Print @sql execute sp_executesql @sql 例程： Create Table 成绩表 ( 编号 int identity(1,1), 姓名 varchar(50), 课程 varchar(50), 成绩 int ) Go Insert Into 成绩表(姓名,课程,成绩) Select '张三','语文',55 union Select '张三','数学',66 union Select '张三','英语',77 union Select '李四','语文',88 union Select '李四','数学',99 union Select '李四','英语',100 union Select '王五','语文',44 union Select '王五','数学',33 union Select '马六','英语',22 union Select '马六','语文',11 Go Select * From 成绩表 Go Select 姓名, '语文'=sum((case when 课程='语文' then 成绩 else 0 end)), '数学'=sum((case 课程 when '数学' then 成绩 else 0 end)), '英语'=sum((case 课程 when '英语' then 成绩 else 0 end)) From 成绩表 Group By 姓名 Go Select 姓名, sum(case when 课程='语文' then 成绩 else 0 end) As '语文' , sum(case when 课程='数学' then 成绩 else 0 end) As '数学', sum(case when 课程='英语' then 成绩 else 0 end) As '英语' From 成绩表 Group By 姓名 Go declare @sql nvarchar(4000) set @sql='Select 姓名 ' Select @sql=@sql+',sum(case when 课程='''+课程+ ''' then 成绩 else 0 end) As '''+课程+'''' From 成绩表 Group By 课程 --Print @sql set @sql=@sql+' From 成绩表 Group By 姓名' --Print @sql execute sp_executesql @sql