自从看《
学技术的不能自废武功》这篇文章后,我深有感触,于是对自己以前学过的知识又重新温习了一次,结果终于发现了一些新的感悟,现将其感悟发表,于大家共勉:
关于
sql
行转列的感悟
(
表1)
要将其转变为:
(
表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中,如何在Group By中呢?
数据倒是有了,可是行多了点,看来只能从聚合入手了。
到底是那个聚合?聚合有max, min , avg , sum , count ,max和min首先干掉,我们不是要最大值,最小值,难道是平均值?难道是个数?难道是求和???
那就试试呗。
Select 姓名, sum ( case when 科目 = ' 语文 ' then 成绩 end ) From 表1 Group By 姓名
嘿,真不错。
该有的都有了,就是没列名,好办,给个别名嘛。
Select 姓名, sum ( case when 科目 = ' 语文 ' then 成绩 end ) As ' 语文 ' From表1 Group By 姓名
那么其他几列也就好办了。
Select 姓名,
sum ( case when 科目 = ' 语文 ' then 成绩 end ) As ' 语文 ' ,
sum ( case when 科目 = ' 数学 ' then 成绩 end ) As ' 数学 ' ,
sum ( case when 科目 = ' 英语 ' then 成绩 end ) As ' 英语 '
From 表1 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 表1 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 表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 ' 英语 '
……………..
那不简单,就是些字符串嘛。
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 ' 英语 '
哈哈,只要将这一段和刚才的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
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 表1 Group By 姓名
嘿,真不错。
该有的都有了,就是没列名,好办,给个别名嘛。
Select 姓名, sum ( case when 科目 = ' 语文 ' then 成绩 end ) As ' 语文 ' From表1 Group By 姓名
那么其他几列也就好办了。
Select 姓名,
sum ( case when 科目 = ' 语文 ' then 成绩 end ) As ' 语文 ' ,
sum ( case when 科目 = ' 数学 ' then 成绩 end ) As ' 数学 ' ,
sum ( case when 科目 = ' 英语 ' then 成绩 end ) As ' 英语 '
From 表1 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 表1 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 表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 ' 英语 '
……………..
那不简单,就是些字符串嘛。
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 ' 英语 '
哈哈,只要将这一段和刚才的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
功德圆满。
本文出自 “魔蝎阿印” 博客,请务必保留此出处http://mikoo.blog.51cto.com/627637/124516
本文转自温景良(Jason)博客园博客,原文链接:http://www.cnblogs.com/wenjl520/archive/2009/12/09/1619887.html,如需转载请自行联系原作者