自从看《
学技术的不能自废武功》这篇文章后，我深有感触，于是对自己以前学过的知识又重新温习了一次，结果终于发现了一些新的感悟，现将其感悟发表，于大家共勉：
关于
sql
行转列的感悟
sql
行转列的感悟
(
表
1)
表
1)
要将其转变为：
(
表
2)
表
2)
可以看得出来，表
2
列是由表
1
的行数据转化而来的，只有姓名列是表
1
中的列。
2
列是由表
1
的行数据转化而来的，只有姓名列是表
1
中的列。
一步一步的来嘛，其他的列难搞，如果表
2
只有
1
列姓名的话，那么
very easy
了，不就是一个简单的分组嘛。
2
只有
1
列姓名的话，那么
very easy
了，不就是一个简单的分组嘛。
Select
姓名
From
表
1 Group By
姓名
姓名
From
表
1 Group By
姓名
现在
sql
语句的架子搭起来了，无论以后如何变化，分组是少不了的。
sql
语句的架子搭起来了，无论以后如何变化，分组是少不了的。
那么开始下一步，想这个结果集中再添加
1
列，多了我们不加，因为你不论是能处理语文，还是数学，还是英语列，那么其他的列只要原样照抄就可以了，顶多修改一下参数而已。
1
列，多了我们不加，因为你不论是能处理语文，还是数学，还是英语列，那么其他的列只要原样照抄就可以了，顶多修改一下参数而已。
从语文列开始吧，这一列的数据都可以从表
1
中找到，如果我们只要“语文”这一列，那么也好写。
1
中找到，如果我们只要“语文”这一列，那么也好写。
Sql
语句疑似：
语句疑似：
Select
姓名
,case
when
科目
='
语文
' then
成绩
end From
成绩表
Group By
姓名
姓名
,case
when
科目
='
语文
' then
成绩
end From
成绩表
Group By
姓名
那就
F5
吧。
F5
吧。
嗯，啊，
...
挂了。
...
挂了。
赶紧看错误提示：
服务器
:
消息
8120
，级别
16
，状态
1
，行
1
列
'
成绩表
.
成绩
'
在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在
GROUP BY
子句中。
服务器
:
消息
8120
，级别
16
，状态
1
，行
1
列
'
成绩表
.
科目
'
在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在
GROUP BY
子句中。
:
消息
8120
，级别
16
，状态
1
，行
1
列
'
成绩表
.
成绩
'
在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在
GROUP BY
子句中。
服务器
:
消息
8120
，级别
16
，状态
1
，行
1
列
'
成绩表
.
科目
'
在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在
GROUP BY
子句中。
从错误信息中，可知字段成绩和科目还必须在聚合函数中或
Group By
中，如何在
Group By
中呢？
Group By
中，如何在
Group By
中呢？
数据倒是有了，可是行多了点，看来只能从聚合入手了。
到底是那个聚合？聚合有
max,min,avg,sum,count
，
max
和
min
首先干掉，我们不是要最大值，最小值，难道是平均值？难道是个数？难道是求和？？？
max,min,avg,sum,count
，
max
和
min
首先干掉，我们不是要最大值，最小值，难道是平均值？难道是个数？难道是求和？？？
那就试试呗。
Select
姓名
,sum(case when
科目
='
语文
' then
成绩
end) From
表
1 Group By
姓名
姓名
,sum(case when
科目
='
语文
' then
成绩
end) From
表
1 Group By
姓名
嘿，真不错。
该有的都有了，就是没列名，好办，给个别名嘛。
Select
姓名
,sum(case when
科目
='
语文
' then
成绩
end) As '
语文
' From
表
1 Group By
姓名
姓名
,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
姓名
姓名
,
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
语句只能支持科目表中只有语文数学英语，那万一课程中加了一个叫物理的，那你不得改程序了吗？！
怎么办？凉拌。
也就是说这个
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
啊。
As '
语文
'
”，哪些个“科目
='
语文
'
”，其实是数据表中的值，如果仅仅要查询“语文，数学，英语”的话，那么
easy
啊。
Select
课程
From
表1
Group By 科目
课程
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 '
英语
'
……………..
科目
='
语文
' 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
课程
科目
='''+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 '
英语
'
科目
='
语文
' then
成绩
else 0 end) As '
语文
' ,
,sum(case when
科目
='
数学
' then
成绩
else 0 end) As '
数学
',
,sum(case when
科目
='
英语
' then
成绩
else 0 end) As '
英语
'
哈哈，只要将这一段和刚才的
Select
姓名拼在一起就可以了。
Select
姓名拼在一起就可以了。
现在麻烦来了，拼没有问题。
declare @sql nvarchar(4000)
set @sql='Select
姓名
'
姓名
'
Select @sql=@sql+',sum(case when
科目
='''+
科目
+ ''' then
成绩
else 0 end) As '''+
科目
+''''
科目
='''+
科目
+ ''' 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 '
语文
'
姓名
,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
姓名
姓名
,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
这个变量中了，怎么执行？
sql
语句放在了
@sql
这个变量中了，怎么执行？
灵机
1
动！
1
动！
SqlServer
中有个专门将字符串作为
sql
语句执行的存储过程
---- sp_executesql
。
中有个专门将字符串作为
sql
语句执行的存储过程
---- sp_executesql
。
就是它了。
最终版本：
declare @sql nvarchar(4000)
set @sql='Select
姓名
'
姓名
'
Select @sql=@sql+',sum(case when
科目
='''+
科目
+ ''' then
成绩
else 0 end) As '''+
科目
+''''
科目
='''+
科目
+ ''' then
成绩
else 0 end) As '''+
科目
+''''
From
成绩表
Group By
科目
Print @sql
set @sql=@sql+' From
成绩表
Group By
姓名
'
成绩表
Group By
姓名
'
Print @sql
execute sp_executesql @sql
当然科目信息，不一定会从成绩表中获取，很有可能是一个专门的表格，比如叫“科目表”，那么就更
easy
了。
easy
了。
declare @sql nvarchar(4000)
set @sql='Select
姓名
'
姓名
'
Select @sql=@sql+',sum(case when
科目
='''+
科目
+ ''' then
成绩
else 0 end) As '''+
科目
+''''
科目
='''+
科目
+ ''' then
成绩
else 0 end) As '''+
科目
+''''
From
科目表
Print @sql
set @sql=@sql+' From
表
1 Group By
姓名
'
表
1 Group By
姓名
'
Print @sql
execute sp_executesql @sql
功德圆满。
本文出自 “射手阿印
” 博客，请务必保留此出处http://mikoo.blog.51cto.com/627637/124516