table 行转列的sql详解
时间:2024-01-27 00:44:57
一、要求
1 创建数据表
CREATE TABLE [dbo].[StuScore](
[stuid] [int] NOT NULL,
[subject] [nvarchar](30) NULL,
[score] [decimal](5, 1) NULL
)
2 插入测试数据
stuid subject score
3 chinese 76.0
3 math 73.0
4 chinese 82.0
5 chinese 66.0
5 math 93.0
6 chinese 67.0
7 math 83.0
8 chinese 77.0
8 math 84.0
3 行转列后的结果
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
二 、分析
1 行转列,一个重点就是怎么样知道有多少列,怎么样创建这些列?我们可以先把这个问题搁置,而假设这些列是已知的。 例如示例数据中,可以先假设subject的数据[chinese,math]是已知的,这样问题就简化了许多
2 当已知了chinese,math后,我们至少要先得到转换后的tabel结构
如下;
select stuid, 0 as chinese, 0 as math from dbo.StuScore
结果如下
stuid chinese math
3 0 0
3 0 0
4 0 0
5 0 0
5 0 0
6 0 0
7 0 0
8 0 0
8 0 0
3 接着就需要往这个数据集中去填充chinese, math的数据
select stuid,
case subject when 'chinese' then score else 0 end as chinese,
case subject when 'math' then score else 0 end as math
from dbo.StuScore
结果如下:
stuid chinese math
3 76.0 0.0
3 0.0 73.0
4 82.0 0.0
5 66.0 0.0
5 0.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 0.0
8 0.0 84.0
4 细心的读者会发现步骤3中的结果与我们想要的已经非常接近了,只需再做一个sum()处理,就OK了
select stuid,
sum(case subject when 'chinese' then score else 0 end ) as chinese,
sum(case subject when 'math' then score else 0 end ) as math
from dbo.StuScore group by stuid
得到的正是我们想要的结果
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
是不是现在就已经完成了呢?答案是否定的。前面我们已经说过,是为了简化问题,在假设已经知道了subject数据的情况下,这么处理的,实际上subject的数据是可变的,未知的,接下来就是要解决这个问题了
5 要获取subject的数据其实很简单
select distinct subject from dbo.StuScore
获取以后怎样得到case subject when 'chinese' then score else 0 end 这种语句?
可以根据subject的值去动态的组sql语句
看下面的一段代码
declare @sql varchar(2000)
set @sql=''
select @sql =@sql+ ',case subject when '''+subject+''' then 1 else 0 end as ' + subject
from (select distinct subject from dbo.StuScore) as sub
print @sql
message打印的信息如下:
,case subject when 'chinese' then 1 else 0 end as chinese,case subject when 'math' then 1 else 0 end as math
6 最后我们就需要将前面步骤综合起来,得到最终的sql
declare @sql varchar(2000)
set @sql='select stuid'
select @sql =@sql+ ',sum(case subject when '''+subject+''' then score else 0 end) as ' + subject
from (select distinct subject from dbo.StuScore) as sub
set @sql=@sql + ' from dbo.StuScore group by stuid'
exec(@sql)
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
至此,整个分析过程和结果就都出来了。
初试写文章, 多包涵,指正。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python实现括号匹配方法详解
keras 模型参数,模型保存,中间结果输出操作
![](https://img.aspxhome.com/file/2023/1/84921_0s.jpg)
MySQL日志的详细分析实例
![](https://img.aspxhome.com/file/2023/4/116744_0s.png)
Flask 数据库集成的介绍
![](https://img.aspxhome.com/file/2023/3/63983_0s.png)
解决Vue watch里调用方法的坑
JSP + Servlet实现生成登录验证码示例
![](https://img.aspxhome.com/file/2023/9/56249_0s.png)
Python3.6.0+opencv3.3.0人脸检测示例
![](https://img.aspxhome.com/file/2023/1/121441_0s.png)
如何在不支持数据库的ASP空间里获取更大的数据库权限?
sqlserver连接错误之SQL评估期已过的问题解决
![](https://img.aspxhome.com/file/2023/2/124852_0s.png)
Python 模拟死锁的常见实例详解
![](https://img.aspxhome.com/file/2023/0/127730_0s.jpg)
手把手教你使用Python解决简单的zip文件解压密码
![](https://img.aspxhome.com/file/2023/4/117894_0s.jpg)
利用PyQt5模拟实现网页鼠标移动特效
![](https://img.aspxhome.com/file/2023/9/92009_0s.png)
Django之使用内置函数和celery发邮件的方法示例
![](https://img.aspxhome.com/file/2023/2/120592_0s.png)
Vue下滚动到页面底部无限加载数据的示例代码
SQL Server正则表达式 替换函数应用详解
JavaScript使用ZeroClipboard操作剪切板
实例讲解如何配置MySQL数据库主从复制
对比MySQL中int、char以及varchar的性能
SQL Server中使用Linkserver连接Oracle的方法
![](https://img.aspxhome.com/file/2023/9/113569_0s.png)
Python I/O与进程的详细讲解
![](https://img.aspxhome.com/file/2023/4/133524_0s.png)