SQL行专列
2010-04-14 09:33 markwangxm 阅读(1224) 评论(0) 编辑 收藏 举报
自从看《学技术的不能自废武功》这篇文章后,我深有感触,于是对自己以前学过的知识又重新温习了一次,结果终于发现了一些新的感悟,现将其感悟发表,于大家共勉:
关于 sql 行转列的感悟
![](http://img1.51cto.com/attachment/200901/200901051231124273921.bmp)
(表1)
要将其转变为:
![](http://img1.51cto.com/attachment/200901/200901051231124525578.bmp)
(表2)
可以看得出来,表2列是由表1的行数据转化而来的,只有姓名列是表1中的列。
一步一步的来嘛,其他的列难搞,如果表2只有1列姓名的话,那么very easy了,不就是一个简单的分组嘛。
Select 姓名 From 表1 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 子句中。
服务器: 消息 8120,级别 16,状态 1,行 1
列 '成绩表.科目' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
从错误信息中,可知字段成绩和科目还必须在聚合函数中或Group By中,如何在Group By中呢?
![](http://img1.51cto.com/attachment/200901/200901051231124583875.bmp)
数据倒是有了,可是行多了点,看来只能从聚合入手了。
到底是那个聚合?聚合有max,min,avg,sum,count,max和min首先干掉,我们不是要最大值,最小值,难道是平均值?难道是个数?难道是求和???
那就试试呗。
Select 姓名,sum(case when 科目='语文' then 成绩 end) From 表1 Group By 姓名
![](http://img1.51cto.com/attachment/200901/200901051231124648796.bmp)
嘿,真不错。
该有的都有了,就是没列名,好办,给个别名嘛。
Select 姓名,sum(case when 科目='语文' then 成绩 end) As '语文' From表1 Group By 姓名
![](http://img1.51cto.com/attachment/200901/200901051231124711703.bmp)
那么其他几列也就好办了。
Select 姓名,
sum(case when 科目='语文' then 成绩 end) As '语文' ,
sum(case when 科目='数学' then 成绩 end) As '数学',
sum(case when 科目='英语' then 成绩 end) As '英语'
From 表1 Group By 姓名
sum(case when 科目='语文' then 成绩 end) As '语文' ,
sum(case when 科目='数学' then 成绩 end) As '数学',
sum(case when 科目='英语' then 成绩 end) As '英语'
From 表1 Group By 姓名
![](http://img1.51cto.com/attachment/200901/200901051231124829468.bmp)
可是结果有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 表1 Group By 姓名
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 表1 Group By 姓名
![](http://img1.51cto.com/attachment/200901/200901051231124890921.bmp)
终于修成正果了。
真的?
当然是假的?
怎么假?
这不废话么!你现在写的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 表1 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 '英语'
From 表1 Group By 姓名
红字部分要从哪里来?如果专心看,那么应该能从中间看出来,哪些个“As '语文'”,哪些个“科目='语文'”,其实是数据表中的值,如果仅仅要查询“语文,数学,英语”的话,那么easy啊。
Select 课程 From 表1 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 '英语'
……………..
sum(case when 科目='数学' then 成绩 else 0 end) As '数学',
sum(case when 科目='英语' then 成绩 else 0 end) As '英语'
……………..
那不简单,就是些字符串嘛。
Select ',sum(case when 科目='''+trim(科目)+ ''' then 成绩 else 0 end) As '''+科目+'''' From 表1 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 '英语'
,sum(case when 科目='数学' then 成绩 else 0 end) As '数学',
,sum(case when 科目='英语' then 成绩 else 0 end) As '英语'
哈哈,只要将这一段和刚才的Select 姓名拼在一起就可以了。
现在麻烦来了,拼没有问题。
declare @sql nvarchar(4000)
set @sql='Select 姓名 '
Select @sql=@sql+',sum(case when 科目='''+科目+ ''' then 成绩 else 0 end) As '''+科目+''''
From表1 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 表1 Group By 姓名
现在麻烦的是,如何执行这个字符串,现在我们将sql语句放在了 @sql 这个变量中了,怎么执行?
灵机1动!
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 表1 Group By 姓名'
Print @sql
execute sp_executesql @sql
功德圆满。