sqlserver技术内幕<二> 表运算符之pivot

例一：

在Sql Server的帮助文档中，对Pivot函数是这样解释的：
可以使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合.

对第一次使用PIVOT函数的朋友来说，这样的解释很难让大家理解，下面编辑用PIVOT函数来实现一个行转列的功能，以便让读者更容易理解该函数.

注意：PIVOT是Sql Server2005的新函数，2005前行转列请参看本站：
SQLServer中(行列转换)行转列及列转行且加平均值及汇总值

先创建一个工资表:

Create Table Salary
(
HrName varchar(50),
Monthly varchar(50),
Money money
)

往表中插入数据：

insert into Salary(HrName,Monthly,[Money]) 
select '张三','一月','3000' 
union all
select '张三','二月','3200' 
union all
select '张三','三月','3500' 
union all
select '李四','一月','3800' 
union all
select '李四','二月','4200' 
union all
select '李四','三月','3900'
union all
select '张三','一月','2000'

查看正常的数据：

select * from Salary

结果：

HrName  Monthly Money
张三    一月    3000.00
张三    二月    3200.00
张三    三月    3500.00
李四    一月    3800.00
李四    二月    4200.00
李四    三月    3900.00
张三    一月    2000.00

查看行转列后的数据：

select HrName as '姓名',[一月],[二月],[三月] from Salary 
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) as pvt

结果：

姓名   一月     二月     三月
李四  3800.00   4200.00  3900.00
张三  5000.00   3200.00  500.00

注意：
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) 中的sum([Money])，这里必须是聚合函数，比如是min,max等。
in ([一月],[二月],[三月])中的[一月],[二月],[三月]即为Monthly的Value,又为新结果集的列名.

如果我们将其中的一月改为四月，因为数据源中没有四月的记录，所以四月查询出来应该为Null.
测试：

select HrName as '姓名',[四月],[二月],[三月] from Salary 
pivot(sum([Money]) for Monthly in ([四月],[二月],[三月])) as pvt

结果：

姓名   四月    二月     三月
李四   NULL   4200.00   3900.00
张三   NULL   3200.00   3500.00

例二：

在SQLServer 2000环境中,如果要实现交叉表格报表,主要是靠一系列复杂的 SELECT...CASE 语句.

其实现过程请参阅这里T-SQL 交叉报表(行列互换) 交叉查询 旋转查询

在SQLServer 2005中我们可以使用PIVOT关系运算符来实现行列转换.

还是以学生成绩表来举例:

id姓名 科目 成绩

1 张三 语文 60
2 张三 数学 65
3 张三 外语 70
4 李四 语文 80
5 李四 数学 90
6 李四 外语 85
7 王五 语文 70
8 王五 数学 71
9 王五 外语 75
10 赵六 语文 64
11 赵六 数学 67
12 赵六 外语 76

查询后得出:

姓名 语文数学外语

李四 80  90  85
王五 70  71  75
张三 60  65  70
赵六 64  67  76

--准备数据:

 

select * from sysobjects where [xtype]='u'

go

if exists(select id from sysobjects where name='studentscore')

drop table studentscore--删除与实验冲突的表

go

create table studentscore--创建实验表

(

[id] int identity(1,1),

[name] nvarchar(20) not null,

subject nvarchar(20) not null,

score int not null

)

go

 

select * from studentscore

go

 

--添加实验数据

insert studentscore values ('张三','语文','60');

insert studentscore values ('张三','数学','65');

insert studentscore values ('张三','外语','70');

insert studentscore values ('李四','语文','80');

insert studentscore values ('李四','数学','90');

insert studentscore values ('李四','外语','85');

insert studentscore values ('王五','语文','70');

insert studentscore values ('王五','数学','71');

insert studentscore values ('王五','外语','75');

insert studentscore values ('赵六','语文','64');

insert studentscore values ('赵六','数学','67');

insert studentscore values ('赵六','外语','76');

go

select * from studentscore

go

 

使用 SELECT...CASE 语句实现代码如下

select [name],

语文=max(case

when subject='语文' then score else 0

end),

数学=max(case

when subject='数学' then score else 0

end),

外语=max(case

when subject='外语' then score else 0

end)

from studentscore

group by [name]

结果:

 

下面我们使用PIVOT关系运算符来实现行列转换

select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'

from (select score,subject,[name] from studentscore) as ss

pivot

(

sum(score) for subject in([语文],[数学],[外语])

) as pvt

结果:用较少的代码完成了交叉表格报表

 

============================

对于这种方法要注意的一点是,我们使用sum()聚合函数,表面上没有指定按什么方式分组,但是自动按照name列分组了.

怎么做到的呢?原来pivot关系运算符会根据前面的对象中的列来自行判断,在这个例子中pivot前面的对象是ss,是个子查询,这个子查询中只有三列,score,subject和[name],但是pivot运算符内部使用了score和subject这两列,那么肯定是对[name]分组.

所以我们得出,pivot运算符的分组规则是,跟随对象中的那些不在pivot运算符内部的列:

为了好理解我们再写一个例子:

--在ss这个子查询中,多加一列id

--那么pivot应该按照name和id进行分组

 

 

select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'

from (select score,subject,[name],id from studentscore) as ss

pivot

(

sum(score) for subject in([语文],[数学],[外语])

) as pvt

 

结果:验证了我们的设想

UNPIVOT关系运算符从字面上来看,就知道它的用途正好和PIVOT相反,下面举例说明:

 

if exists(select id from sysobjects where name='studentscore')

drop table studentscore--删除与实验冲突的表

go

create table studentscore--创建实验表

(

[id] int identity(1,1),

[name] nvarchar(20) not null,

yuwen int not null,

shuxue int not null,

waiyu int not null

)

go

 

select * from studentscore

go

 

--添加实验数据

insert studentscore values ('张三','60','65','70');

insert studentscore values ('李四','80','90','86');

insert studentscore values ('王五','70','71','75');

insert studentscore values ('赵六','64','67','76');

go

select * from studentscore

go

 

结果: 

 

 

 

SELECT id, [name],subject, score

FROM

   (SELECT id,[name], 语文=yuwen, 数学=shuxue, 外语=waiyu

   FROM studentscore) as ss

UNPIVOT

   (score FOR subject IN

      (语文, 数学, 外语)

)AS unpvt

结果:

 

使用union all 代替unpivot功能

create  table  test(id  int , name  varchar (20), Q1  int , Q2  int , Q3  int , Q4  int ) 

 

insert  into  test  values (1, 'a' ,1000,2000,4000,5000) 

insert  into  test  values (2, 'b' ,3000,3500,4200,5500) 

 

select  id ,   name  ,quarter = 'Q1'  , profile = Q1  from  test

union  all

select  id ,   name  ,quarter = 'Q2'  , profile = Q2  from  test

union  all

select  id ,   name  ,quarter = 'Q3'  , profile = Q3  from  test

union  all

select  id ,   name  ,quarter = 'Q4'  , profile = Q4  from  test

order  by  id , name  , quarter

 

drop  table  test

 

/*

id           name                  quarter profile     

----------- -------------------- ------- ----------- 

1           a                    Q1      1000

1           a                    Q2      2000

1           a                    Q3      4000

1           a                    Q4      5000

2           b                    Q1      3000

2           b                    Q2      3500

2           b                    Q3      4200

2           b                    Q4      5500