sql server 的行转列

cencenhai

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