数据库

sql server 的行转列

我爱IT资讯库   2021/02/23

sql server中行转列
在sql2005之后,sqlserver引入了pivot运算符,也即是可以旋转行为列,它主要基于实体属性模型模型(EAV)数据库.
EAV模型数据库可以扩展数据库实体,而不需要进行数据库架构的修改。因而,eav存储实体的属性采用键值对模型的表;
举个栗子:
create table eav_table(
Id INT NOT NULL,
Attribute CHAR(100) NOT NULL,
Value SQL_VARIANT NOT NULL,
PRIMARY KEY (Id,Attribute)
)
go;
-- insert
insert eav_table(Id,Attribute,Value),
values
(1,"name","name"),
(1,"last_name","last_name"),
(1,"City","City"),
(1,"Country","Country"),
(2,"name","name2"),
(2,"last_name","last_name2"),
(2,"City","City2"),
(2,"Country","Country2"),
上面这个就是开放数据库架构的“魅力",如果我们想扩展更多实体属性,只需插入额外的记录即可,而不需要数据库架构的修改
那么当我们想实现如下的效果
ID    name    last_name     City     Country
1     name    last_name     City     Country
2     name2    last_name2     City2     Country2
也就意味着 我们需要进行 行转列 ,那么有两种方式可以实现我们的需求
1、使用 pivot 运算符   2、使用case
那么我们接下来进行详细剖析
1、如果采用case  我们需要三个阶段实现 a、分组  b、摊开  c、聚合
在分组阶段将数据库表中分为不同的实体,也就是说我们要对Id进行group by
在摊开阶段将使用case将行转为列
在聚合阶段使用max为每行每列返回不同的结果

select Id,
   max(case when Attribute = 'name' then Value end) as 'first_name',
   max(case when Attribute = 'last_name' then Value end) as 'last_name',
   max(case when Attribute = 'City' then Value end) as 'City',
   max(case when Attribute = 'Country' then Value end) as 'Country'
from eav_table
group by Id
那么上面的sql也就完成了我们的需求

2、pivot运算符
这个运算符从sqlserver 2005引入,至今差不多10年了,使得我们实现行转列只需要一个运算符即可完成。往往说简单的背后隐藏着巨大的”boss bug“
select Id,name, last_name, city , country
from eav_table
pivot(max(Value) FOR Attribute IN (name, last_name,city,country)) as temp
go
那么执行上面的sql同样可以得到我们所需的结果
同时大家也发现在上面的sql中我们只指定分摊和聚合元素,却没有定义分组元素,其实分组元素也是pivot运行符剩下的列
那么随着而来就有新问题了,如果我们修改了数据库架构,新增一列
alter table eav_table add other char(10) go;
接着赋值:update eav_table set other = "test";
再次执行pivot就会出现 我们所不想看到的结果
也就是说如pivot分组不明确,导致结果并非我们所需的,那么我们就需要使用只返回我们所需的列的表表达式。即使后期再修改表 仍然不会对我们现有的结构有影响
select Id,name, last_name, city , country
from (
select Id,name, last_name, city , country
from eav_table
) as temp
pivot(max(Value) FOR Attribute IN (name, last_name,city,country)) as temp
go

小结:pivot运算符确实可以给我们带来非常高效的代码,同时它也具备副作用,不能执行分组元素,因此,需要借助一个表表达式作为辅助完成最终的结果

 




热门内容

【Node.js基础篇】(九)使用readline模块实现Node.js的输入输出

【Node.js基础篇】(九)使用readline模块实现Node.js的输入输出

学C++的时候,有cout和cin,Java也有println和Scanner控件,Node.js也有如同... ...
iOS 应用支持4寸屏

iOS 应用支持4寸屏

iPhone 5出了,一大批应用要兼容新的分辨率,下载了Xcode 4.5,在官方那里找不到有关兼容新分辨... ...

循环执行oracle到mysql的迁移步骤及各种注意事项

改章节笔者在北京逛街的时候突然想到的...今天就有想写几篇关于循环执行的笔记,所以回家到之后就奋笔疾书的写出来发表了 ... ...
2015收获的季节

2015收获的季节

—————————————————————前言————————————————————— ———————... ...

JSP中的pageEncoding和contentType属性

关于JSP页面中的pageEncoding和contentType两种属性的区别: pageEncoding是js... ...

对XX证券报关于物联网操作系统的几个问题的答复

XX证券报提问了几个关于物联网和物联网操作系统的问题,个人表达了一些粗陋的观点,在这里发表出来,与行业朋友交流和探... ...
In语句优化

In语句优化

In语句的优化 现有两张表: CLINIC_ORGANISM 数据量:1400 LAB_DRUGRESISTITE... ...
fuzzysearch, 在JavaScript中,快速而快速的模糊搜索

fuzzysearch, 在JavaScript中,快速而快速的模糊搜索

源代码名称:fuzzysearch源代码网址:http://www.github.com/bevacqua/fuzzys ...

SQL Server里的 ISNULL 与 NULLIF

SQL Server 中有两个参数,语法:     ISNULL(check_... ...

判断SD是否存在及其容量查询

这个非常简单,直接上代码: MainActivity: package com.home.getsdinf... ...

Python:如何排序(sort)

一、前言 python的列表(list)有两个排序方法: 一种是内建的list.sort()方法,可以直接改变列表的内容 ...

boost库在工作(5)作用域智能指针scoped_ptr之四

第二种情况,主要就是使用在调用异常抛出的函数的地方。如下面的例子: //异常抛出的函数,适合使用智能指针... ...

Error:couldnotopen`C:\ProgramFiles\Java\jre6\lib\i386\jvm.cfg'

昨天刚过情人节!哈哈,好久没记录学习内容了,今天在房子闲着没事重新安装jdk,按道理很简单的; 第一步下载jdk; ...

第49周二

晚上总结下今天,主要是在完善用户需求文档,同时看了jquery相关的操作技巧,主要是想学习jquery源码,在知乎jqu ...

C++ #include 和 using std::string

今天,偶尔写了一个小小的程序,关于字符串问题程序。 比如,我想连续打印用户输入的字符串。 #include&l... ...

Struts Hibernate Spring 经典面试题

Hibernate工作原理及为什么要用? 原理: 1.读取并解析配置文件 2.读取并解析映射信息,创建Ses... ...

SAP Performance浅析

本文来源于:http://scnblogs.techweb.com.cn/tcsapbw/archives/106... ...

数据库MySQL与xls文件的互导

      最近的一个项目需要将xls表导入到MySQL数据库中和将MySQL数据表导出到... ...

Head-of-Line Blocking (线头阻塞)

Head of Line (HOL) Blocking 产生的原因: 概念:队列的首个packet由于它的目的... ...

Python set的高效利用

python set的应用   ... ...

CollectionFrameWork

collectionframework如下: collection ├list │├linkedlist │├array ...

JAVA访问修饰符构造函数的问题(转)

java访问修饰符 构造函数的问题 java访问修饰符-限定符总结(类比c#) java访问修饰符--------- ...

PAT甲1004CountingLeaves【dfs】

1004counting leaves(30 分) a family hierarchy is usually p ...

171.[LeetCode]Excel Sheet Column Number

题目: Related to question Excel Sheet Column Title ... ...

在.NET中获取一台电脑名,IP地址及当前用户名

在.NET中获取一台电脑名,IP地址及当前用户名是非常简单,以下是我常用的几种方法,如果大家还有其他好的方法,可以回复一... ...
sun directory server

sun directory server

Sun One Directory Server(LDAP)安装和调整指南   ... ...
黑客讲故事:攻下隔壁女生路由器后,我都做了些什么

黑客讲故事:攻下隔壁女生路由器后,我都做了些什么

路由器被蹭网后,我有被黑的风险吗? Evi1m0,来自知道创宇,邪红色信息安全组织创始人 其实这个问题可以... ...

oracle 10g for redhat5

解压文件 解压文件命令: unzip 10201_database_linux32.zip ... ...

什么是I2C协议?

I2C协议是单片机与其它芯片常用的通讯协议,由于只需要两根线,所以很好使用。 一. I2C协议技术性能:&nb... ...

[USACO15DEC]最大流MaxFlow

题目:洛谷p3128。 题目大意:一棵n个点的树,每次将两个节点最短路径所覆盖的所有节点的流量加1。问你最后流量最大的节 ...