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
至此,整个分析过程和结果就都出来了。
初试写文章, 多包涵,指正。

标签:table,行转列
0
投稿

猜你喜欢

  • Python实现括号匹配方法详解

    2021-05-01 23:06:58
  • keras 模型参数,模型保存,中间结果输出操作

    2023-06-05 09:52:33
  • MySQL日志的详细分析实例

    2024-01-25 21:35:52
  • Flask 数据库集成的介绍

    2023-07-15 09:28:20
  • 解决Vue watch里调用方法的坑

    2024-05-05 09:10:44
  • JSP + Servlet实现生成登录验证码示例

    2023-06-27 02:47:59
  • Python3.6.0+opencv3.3.0人脸检测示例

    2023-07-23 05:51:07
  • 如何在不支持数据库的ASP空间里获取更大的数据库权限?

    2010-06-18 19:35:00
  • sqlserver连接错误之SQL评估期已过的问题解决

    2024-01-14 07:25:27
  • Python 模拟死锁的常见实例详解

    2022-08-02 04:57:18
  • 手把手教你使用Python解决简单的zip文件解压密码

    2021-01-20 10:08:41
  • 利用PyQt5模拟实现网页鼠标移动特效

    2022-11-17 10:46:01
  • Django之使用内置函数和celery发邮件的方法示例

    2022-03-04 17:15:26
  • Vue下滚动到页面底部无限加载数据的示例代码

    2024-06-05 15:31:44
  • SQL Server正则表达式 替换函数应用详解

    2024-01-28 06:01:16
  • JavaScript使用ZeroClipboard操作剪切板

    2024-04-29 13:38:14
  • 实例讲解如何配置MySQL数据库主从复制

    2008-12-17 15:33:00
  • 对比MySQL中int、char以及varchar的性能

    2024-01-22 13:17:52
  • SQL Server中使用Linkserver连接Oracle的方法

    2024-01-23 07:35:00
  • Python I/O与进程的详细讲解

    2022-11-27 14:07:15
  • asp之家 网络编程 m.aspxhome.com