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运算符确实可以给我们带来非常高效的代码,同时它也具备副作用,不能执行分组元素,因此,需要借助一个表表达式作为辅助完成最终的结果
相关推荐
Sql Server里面的行转列,列转行
列转行行转列
sqlserver 实现 行转列 split 分割的函数,具体使用方法写有在文件里。
sqlserver 动态行专列 避免了数据列过多的时候大量的使用case when then...... 原数据 : UserName Subject Score Nick 语文 80 Nick 数学 90 Nick 英语 70 Nick 生物 85 Kent 语文 80 Kent 数学 90 Kent ...
NULL 博文链接:https://zmx.iteye.com/blog/570073
SqlServer如何进行行转列和列转行方法
本文主要介绍了SQL Server行转列的方法解析。具有很好的参考价值。下面跟着小编一起来看下吧
SQL行转列参考代码(sqlserver)
SQL 行转列+动态获取列名 通用的动态获取列名
SQLServer行转列的sql语句有很多,可以使用静态的、动态的、Case When等等,均能实现
向大家介绍 SQL Server 中行转列问题的终极解决方案,主要应用case语句来解决行转列的问题
行转列sql实例行转列sql实例行转列sql实例行转列sql实例行转列sql实例
SQL之行转列 SQL之行转列 SQL之行转列
sql行转列解决方案.
sqlserver 行转成列 sqlserver 把行转成列 sqlserver 把行转成列
sql行变列说明
sqlserver 行数据转成列数据,#temp1为数据表,#temp2为辅助表,具体场景可微调替换。
昨天遇到一个SQL Server的问题:需要写一个储存过程来处理几个表中的数据,最后问题出在我想将一个表的一个列的多行内容拼接成一行,比如表中有两列数据 : 类别 名称 AAA 企业1 AAA 企业2 AAA 企业3 ...
sql行转列_列转行问题.