Pivot研究

      今天学习了SQL2005的PIVOT，PIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。记得我们在SQL2000中要用聚合和CASE语句完成一个行列转换吧，特别当待转成列的数据不定时，我们往往构造动态SQL，然后用EXEC来运行。

环境准备： （下面我引用大乌龟的代码来说明）

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------

 

 

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

 

 

一.我们先看SQL2000中的处理方法：

   在SQL2000中行列转换：我们对上例程把【课程】转列显示，并把【分数】当对应列值。我们分两种情况来讨论:

 

 

1、当【课程】的列值固定就[语文] [数学] [物理] 三种情况

 

 

select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

 

 

 

2、当【课程】的列值不固定时就运用动态SQL ，其实也就是构造一个sum(CASE WHEN ...)SQL 字符串

 

 

 

 

declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)

 

 

 

二.在SQL2005中，我们有更简单的表达方式（PIVOT）。

 

 

1.先来看看2005 的FROM 子句的定义：

 

[ FROM { <table_source> } [ ,...n ] ]

<table_source> ::= 
   { <pivoted_table> }

<pivoted_table> ::=
          table_source PIVOT <pivot_clause> table_alias

<pivot_clause> ::=
        ( aggregate_function ( value_column ) 
        FOR pivot_column 
        IN ( <column_list> ) 
        )

<column_list> ::=
         column_name [ , ... ]

 

 

pivot_column 和value_column 是PIVOT运算符使用的组合列。

 

PIVOT遵循以下过程获得输出结果集：

(Ⅰ) 对分组列的input_table 执行GROUP BY ，为每个组生成一个输出行。

(Ⅱ) 输出行中的分组列获得input_table 中该组的对应列值。

(Ⅲ) 通过执行以下操作，为每个输出行生成列列表中的列的值：

     a 针对pivot_column，对上一步在GROUP BY 中生成的行另外进行分组。

     b 对于column_list中的每个输出列，选择满足以下条件的子组：

pivot_column = CONVERT(<data type of pivot_column>, 'output_column')，针对此子组上的aggregate_function对value_column 求值，其结果作为相应的output_column 的值返回。如果该子组为空，SQL Server 将为该output_column生成空值。如果聚合函数是 COUNT ，且子组为空，则返回零(0)。

      接着我们利用我们开头的例子来理解一下这个FROM 子句，很显然我们的【分数】对应上面的value_column, 我们还假定列会下固定为这三项，那么列【课程】对应上面的pivot_column, 进而我们应该得出[语文] [数学] [物理]是column_name 即我们的输出列，最后我们只要构造一下table_source 就可以了，如何构造这个table_source ，显然pivot_column 和value_column 应该包含在其中，其它就应该是你想要分组的列啦.

 

    我们来总结一下：这个FROM 子句是基于 table_source 对 pivot_column 进行透视，table_source 中 pivot_column 和 value_column 列之外的列被称为透视运算符的组合列, 而PIVOT 是对输入表执行组合列的分组操作，并为每个组返回一行，好，我们试着写出这个SQL ：

静态SQL：

select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

动态SQL：

declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

三.下面我们用例子分析下PIVOT

1.原始数据：

 

 

姓名         课程         分数          测试
---------- ---------- ----------- ----------
张三         语文         74          测试1
张三         数学         83          测试2
张三         物理         93          测试3
李四         语文         74          测试4
李四         数学         84          测试5
李四         物理         94          测试6

 

 

执行语句：

select * from tb pivot (max(分数) for 课程in (语文,数学,物理)) b

结果：

姓名         测试         语文          数学          物理
---------- ---------- ----------- ----------- -----------
张三         测试1        74          NULL        NULL
张三         测试2        NULL        83          NULL
张三         测试3        NULL        NULL        93
李四         测试4        74          NULL        NULL
李四         测试5        NULL        84          NULL
李四         测试6        NULL        NULL        94

(6 行受影响)

2.原始数据：

姓名         课程         分数          测试
---------- ---------- ----------- ----------
张三         语文         74          测试1
张三         数学         83          测试1
张三         物理         93          测试3
李四         语文         74          测试3
李四         数学         84          测试5
李四         物理         94          测试5

 

 

执行语句：

select * from tb pivot (max(分数) for 课程in (语文,数学,物理)) b

结果：

姓 名         测试         语文          数学          物理
---------- ---------- ----------- ----------- -----------
张三         测试1        74          83          NULL
李四         测试3        74          NULL        NULL
张三         测试3        NULL        NULL        93
李四         测试5        NULL        84          94

(4 行受影响)

看出来了吧？先基于姓名和测试分组，再按照课程创建列，填如对应的分数，没有对应数据的补NULL。

这样，在上面两种源数据的情况下，最初我们想要得到的数据也很容易查得。

 

 

select * from (SELECT [姓名],[课程],[分数]FROM [tb])a

   pivot (max(分数) for 课程in (语文,数学,物理)) b

 

 

姓名         语文          数学          物理
---------- ----------- ----------- -----------
李四         74          84          94
张三         74          83          93

(2 行受影响)