table 行转列的sql详解

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

赞 (0)

相关推荐

  • MySQL存储过程中使用动态行转列

    MySQL存储过程中使用动态行转列

    本文介绍的实例成功的实现了动态行转列.下面我以一个简单的数据库为例子,说明一下. 数据表结构 这里我用一个比较简单的例子来说明,也是行转列的经典例子,就是学生的成绩 三张表:学生表.课程表.成绩表 学生表 就简单一点,学生学号.学生姓名两个字段 CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号', `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名', PRIMARY KEY (`

  • sql动态行转列的两种方法

    第一种方法: 复制代码 代码如下: select *from ( select Url,case  when  Month=01 then  '1月' when  Month=02 then '2月' when  Month=03 then  '3月' when  Month=04 then '4月' when  Month=05 then  '5月' when  Month=06 then '6月' when  Month=07 then  '7月' when  Month=08 then '

  • SQL Server行转列的方法解析

    话不多说,请看代码: /* SELECT <非透视的列>, [第一个透视的列] AS <列名称>, [第二个透视的列] AS <列名称>, ... [最后一个透视的列] AS <列名称>, FROM (<生成数据的 SELECT 查询>) AS <源查询的别名> PIVOT ( <聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列], [第二个透视的列

  • mssql 数据库表行转列，列转行终极方案

    复制代码 代码如下: --行转列问题 --建立測試環境 Create Table TEST (DATES Varchar(6), EMPNO Varchar(5), STYPE Varchar(1), AMOUNT Int) --插入數據 Insert TEST Select '200605', '02436', 'A', 5 Union All Select '200605', '02436', 'B', 3 Union All Select '200605', '02436', 'C', 3

  • SQLServer行转列实现思路记录

    SQLServer行转列实现思路记录

    最近面试遇到了一道面试题,顿时有点迷糊,只说出了思路,后来百度了一下,整理了一下思路,于是记录下来,方便以后学习.(面试题请参见附件) 相关的数据表: 1.Score表  2.[User]表  SQL语句如下: --方法一:静态SQL 复制代码 代码如下: SELECT * FROM (SELECT UID,Name, Score,ScoreName FROM Score,[User] WHERE Score.UID=[User].ID) AS SourceTable PIVOT(AVG(Sco

  • sql多表行转列、级联行转列示例代码

    现有两表A,B A表存储商品点击日志,B表存储商品 要求显示当天所有商品点击量列表并附带总数并按天排序 复制代码 代码如下: declare @sql varchar(2000) set @sql='select CONVERT(varchar(100), a.[Time], 23) as 时间,count(b.title) as 总数' select @sql =@sql+ ',sum(case b.title when '''+Title+''' then 1 else 0 end) as

  • SQL查询语句行转列横向显示实例解析

    SQL查询语句行转列横向显示实例解析

    本文分享了两个有关SQL查询语句行转列横向显示的示例,供大家参考,具体内容如下 示例1: 在SQL查询语句行转列横向显示中access中没有CASE,要用IIF代替 select  iif(sex= '1 ', '男 ', '女 ')  from  tablename select country, sum(case when type='A' then money end) as A, sum(case when type='B' then money end) as B, sum(case

  • Sql Server 2000 行转列的实现（横排）

    Sql Server 2000 行转列的实现（横排）

    我们用到的表结构如下: 三张表的关系为: 现有的测试数据为: 我们需要的结果是: 复制代码 代码如下: DECLARE @strSQL VARCHAR(8000) SET @strSQL = 'SELECT t.STUNAME [姓名]' SELECT @strSQL = @strSQL + ',SUM(CASE s.SNAME WHEN ''' + SNAME + ''' THEN g.[Score] END) [' + SNAME + ']' FROM (SELECT SNAME FROM

  • table 行转列的sql详解

    一.要求 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

  • SQL行转列和列转行代码详解

    行列互转,是一个经常遇到的需求.实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现. 在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过.为了加深认识,再总结一次. 行列互转,可以分为静态互转,即事先就知道要处理多少行(列);动态互转,事先不知道处理多少行(列). --创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orde

  • 行转列之SQL SERVER PIVOT与用法详解

    在数据库操作中,有些时候我们遇到需要实现"行转列"的需求,例如一下的表为某店铺的一周收入情况表: WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL) 我们先插入一些模拟数据: INSERT INTO WEEK_INCOME SELECT '星期一',1000 UNION ALL SELECT '星期二',2000 UNION ALL SELECT '星期三',3000 UNION ALL SELECT '星期四',4000 UNION ALL SE

  • 一条sql详解MYSQL的架构设计详情

    一条sql详解MYSQL的架构设计详情

    目录 1 前言 2 应用层 2.1 连接线程处理 3 服务层 3.1 SQL 接口 3.2 SQL解析器 3.3 SQL优化器 3.4 执行器 3.5 查询缓存 4 存储引擎层 4.1 概述 4.2 缓冲池(buffer pool) 4.2.1 数据页.缓存页和脏页 4.2.2 元数据 4.2.3 free链表 4.2.4 flush链表 4.2.5 LRU链表 4.2.6 小结 4.3 undo log 4.4 redo log 5 总结 1 前言 对于一个服务端开发来说 MYSQL 可能是他

  • python文件特定行插入和替换实例详解

    python文件特定行插入和替换实例详解 python提供了read,write,但和很多语言类似似乎没有提供insert.当然真要提供的话,肯定是可以实现的,但可能引入insert会带来很多其他问题,比如在插入过程中crash掉可能会导致后面的内容没来得及写回. 不过用fileinput可以简单实现在特定行插入的需求: Python代码 import os import fileinput def file_insert(fname,linenos=[],strings=[]): ""

  • C++ 线程（串行 并行 同步 异步）详解

    C++ 线程（串行 并行 同步 异步）详解

    C++  线程(串行 并行 同步 异步)详解 看了很多关于这类的文章,一直没有总结.不总结的话就会一直糊里糊涂,以下描述都是自己理解的非官方语言,不一定严谨,可当作参考. 首先,进程可理解成一个可执行文件的执行过程.在ios app上的话我们可以理解为我们的app的.ipa文件执行过程也即app运行过程.杀掉app进程就杀掉了这个app在系统里运行所占的内存. 线程:线程是进程的最小单位.一个进程里至少有一个主线程.就是那个main thread.非常简单的app可能只需要一个主线程即UI线程.

  • mybatis的动态sql详解（精）

    MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力.如果你有使用 JDBC 或其他 相似框架的经验,你就明白条件地串联 SQL 字符串在一起是多么的痛苦,确保不能忘了空 格或在列表的最后省略逗号.动态 SQL 可以彻底处理这种痛苦. 通常使用动态SQL不可能是独立的一部分,MyBatis当然使用一种强大的动态SQL语言来改进这种情形,这种语言可以被用在任意映射的SQL语句中. 动态SQL元素和使用 JSTL或其他相似的基于XML的文本处理器相似.在MyBatis之前的版本中,有很多

  • Python中pandas dataframe删除一行或一列:drop函数详解

    用法:DataFrame.drop(labels=None,axis=0, index=None, columns=None, inplace=False) 在这里默认:axis=0,指删除index,因此删除columns时要指定axis=1: inplace=False,默认该删除操作不改变原数据,而是返回一个执行删除操作后的新dataframe: inplace=True,则会直接在原数据上进行删除操作,删除后就回不来了. 例子: >>>df = pd.DataFrame(np.a

  • 对python numpy.array插入一行或一列的方法详解

    如下所示: import numpy as np a = np.array([[1,2,3],[4,5,6],[7,8,9]]) b = np.array([[0,0,0]]) c = np.insert(a, 0, values=b, axis=0) d = np.insert(a, 0, values=b, axis=1) print(c) print(d) >>c [[0 0 0] [1 2 3] [4 5 6] [7 8 9]] >>d [[0 1 2 3] [0 4 5

  • Python pandas 列转行操作详解(类似hive中explode方法)

    最近在工作上用到Python的pandas库来处理excel文件,遇到列转行的问题.找了一番资料后成功了,记录一下. 1. 如果需要爆炸的只有一列: df=pd.DataFrame({'A':[1,2],'B':[[1,2],[1,2]]}) df Out[1]: A B 0 1 [1, 2] 1 2 [1, 2] 如果要爆炸B这一列,可以直接用explode方法(前提是你的pandas的版本要高于或等于0.25) df.explode('B') A B 0 1 1 1 1 2 2 2 1 3

随机推荐