sql server 纵横表的转换
在平常的工作中或者面试中,我们可能有遇到过数据库的纵横表的转换问题。今天我们就来讨论下。
1.创建表
首先我们来创建一张表。
sql语句:
--1. 创建数据表
if OBJECT_ID('Score') is not null drop table Score create table Score
(
姓名 nvarchar(128),
课程 nvarchar(128),
分数 int
) insert into Score values('张三','语文',98)
insert into Score values('张三','数学',89)
insert into Score values('张三','物理',78)
insert into Score values('李四','语文',79)
insert into Score values('李四','数学',88)
insert into Score values('李四','物理',100) select * from Score
执行结果:
2. 传统的纵横表转换
2.1 纵表转横表
先看看我们要转成的横表张什么样子:
既然这个表只有两列,那么可以根据姓名进行分组。先把姓名拼凑出来,后面的分数我们再想办法。
sql:
select t.姓名 2 from Score as t 3 group by t.姓名
结果:
分析:
- 我们先拿到语文这个科目的分数。既然我们用到了group by 语句,这里肯定要用聚合函数来求分数。
- 而且我们只需要语文这一科的成绩,分组出来的 一共有 3列 ,分别是 语文、数学、物理 。 那么就需要判断科目来取分数。
这里符合我们需求的 case 语句就登场了。他和c#中switch-case 作用一样。
sql case 语句语法:
case 字段
when 值1 then 结果
when 值2 then 结果2
...
else 默认结果
end
求语文的分数就简单了:
select t.姓名,
SUM(case t.课程 when '语文' then t.分数 else 0 end) as 语文
from Score as t
group by t.姓名
结果:
既然语文的分数取到了,其他科目改变下条件就可以了。
完整的sql:
select t.姓名,
SUM(case t.课程 when '语文' then t.分数 else 0 end) as 语文,
SUM(case t.课程 when '数学' then t.分数 else 0 end) as 数学,
SUM(case t.课程 when '物理' then t.分数 else 0 end) as 物理
from Score as t
group by t.姓名
OK,到这儿,我们传统方式的纵表转横表就大功告成了。
2.2 横表转纵表
那么我们可以把转换过来的横表再转换回去吗?
我们先把刚刚转好的表,插入一个新表ScoreHb 中。
-- 转换的表插入新表
select t.姓名,
SUM(case t.课程 when '语文' then t.分数 else 0 end) as 语文,
SUM(case t.课程 when '数学' then t.分数 else 0 end) as 数学,
SUM(case t.课程 when '物理' then t.分数 else 0 end) as 物理
into ScoreHb
from Score as t
group by t.姓名
这时ScoreHb 就是我们刚转换好的横表,我们再想办法把他转回来。
怎么转呢? 一步步来。我们也先把张三和李四的语文成绩查出来。
sql:
--张三李四语文的分数
select t.姓名,
'语文' as 课程,
t.语文 as 分数
from ScoreHb as t
结果:
还有两科的数据怎么办呢? 很简单,我们一个个都查出来,然后用 union all 把他们组合为一张表就可以了。
sql:
-- union all链接3个科目
select t.姓名,
'语文' as 课程,
t.语文 as 分数
from ScoreHb as t
union all
select t.姓名,
'数学' as 课程,
t.数学 as 分数
from ScoreHb as t
union all
select t.姓名,
'物理' as 课程,
t.物理 as 分数
from ScoreHb as t
order by t.姓名 desc
结果:
这样,我们就把表又变回去了。
但是大家有没有觉得很麻烦呢?别急,我们有更简单的办法。下面为大家介绍pivot关系运算符。
3. 用pivot和unpivot运算符进行转换
pivot是sql server 2005 提供的运算符,所以只要数据库在05版本以上的都可以使用。主要用于行和列的转换。
3.1 pivot纵表转横表
sql:
select
t2.姓名,
t2.数学,
t2.物理,
t2.语文
from Score as t1
pivot (sum(分数) for 课程 in(数学,语文,物理)) as t2
结果:
是不是代码简洁多了。
pivot将原来表中 课程字段中的 数据行 数学,语文,物理 转换为列,并用sum取对应列的值。
我们只需要记住它的用法就可以了。
3.2 unpivot 横表转纵表
既然有privot可以纵表转横表。那么有没有运算符帮我们转回来呢?
答案是肯定的,他就是unpivot
sql:
select
*
from
ScoreHb
unpivot (分数 for 课程 in (语文,数学,物理)) as t4
结果:
unpivot 将 语文,数学,物理 列转为行,分数为新的一列存放对应的值。
是不是比我们之前一个个表查询拼接,方便了很多。
最新文章
- golang DynamoDB sdk AccessDeniedException
- 理解LSTM网络
- jQuery使用经验建议
- Iphone 英语语言下通讯录排序问题
- 运行时报错-Verify the Developer App certificate for youraccount is trusted on your device. Open Settings on Mayoyi_sakura and navigate to General ->; Device Management, then select your
- Servlet过滤器和监听器
- 十、mysql事务的简介
- C/C++:拷贝构造函数
- Ajax在PC端可以使用但在手机端不能使用
- 【转】Log4cpp 封装
- 【Chromium中文文档】Web安全研究
- github本地库及clone常用命令
- Windows Server 2012 安装sqlserver2008 小记
- JavaScript获取浏览器版本等信息
- 利用Node的chokidar 监听文件改变的文件。
- Spring boot打包war包
- Lesson 29 Taxi!
- Java 基础 Java平台的3个版本
- swift 加载 本地html 和 网络路径
- P1067 多项式输出
热门文章
- 内置函数(Day16)
- 曾经跳过的坑----js截取字符串substr与substring 和 trim
- HAProxy详解
- PHP生成缩略图,控制图片质量,支持.png .jpg .gif
- Linux sh远程连接失败 sshd.service启动失败
- 使用concurrent.futures和ProcessPoolExecutor来替代线程和进程
- 20145240 《Java程序设计》第三次实验报告
- C++使用命名空间中成员的三种方式
- hive学习7(条件函数case)
- sqlserver设置具体的访问权限