深入SQL中PIVOT 行列转换详解
PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值。
通俗简单的说:PIVOT就是行转列,UNPIVOT就是列传行
一、PIVOT实例
1. 建表
建立一个销售情况表,其中,year字段表示年份,quarter字段表示季度,amount字段表示销售额。quarter字段分别用Q1, Q2, Q3, Q4表示一、二、三、四季度。
1 2 3 4 5 |
|
2. 填入表数据
使用如下程序填入表数据。
1 2 3 4 5 6 7 8 9 10 11 |
|
3、如果我们要比较每年中各季度的销售状况,要怎么办呢?有以下两种方法:
(1)、使用传统Select的CASE语句查询
在SQL Server以前的版本里,将行级数据转换为列级数据就要用到一系列CASE语句和聚合查询。虽然这种方式让开发人员具有了对所返回数据进行高度控制的能力,但是编写出这些查询是一件很麻烦的事情。
1 2 3 4 5 6 |
|
得到的结果如下:
(2)、使用PIVOT
由于SQL Server 2005有了新的PIVOT运算符,就不再需要CASE语句和GROUP BY语句了。(每个PIVOT查询都涉及某种类型的聚合,因此你可以忽略GROUP BY语句。)PIVOT运算符让我们能够利用CASE语句查询实现相同的功能,但是你可以用更少的代码就实现,而且看起来更漂亮。
1 |
|
得到的结果如下:
二、通过下面一个实例详细介绍PIVOT的过程
1 2 3 4 5 6 |
|
三.UNPIVOT
很明显,UN这个前缀表明了,它做的操作是跟PIVOT相反的,即列转行。UNPIVOT操作涉及到以下三个逻辑处理阶段。
1,生成副本
2,提取元素
3,删除带有NULL的行
UNPIVOT实例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
|
上面UNPIVOT实例的分析
UNPIVOT的输入是左表表达式P,第一步,先为P中的行生成多个副本,在UNPIVOT中出现的每一列,都会生成一个副本。因为这里的IN子句有5个列名称,所以要为每个来源行生成5个副本。结果得到的虚拟表中将新增一个列,用来以字符串格式保存来源列的名称(for和IN之间的,上面例子是 Employee )。第二步,根据新增的那一列中的值从来源列中提取出与列名对应的行。第三步,删除掉结果列值为null的行,完成这个查询。