SQL表旋转

浏览数：30 / 时间：2015年06月12日

在制作报表的时候，有时候会碰到基础资料是依照时间区间去一笔一笔记录的资料，但是使用者在看报表的时候想要将时间区间以横向的方式呈现不是直向的情况出现，又或者基础资料的表数据结构是横向的而使用者在看报表的时候想以直向方式呈现。这个时候我们就可以采用表旋转技术。所谓的表旋转，就是将表的行转换为列，或是将表的列转换为行.表旋转在某些方面也是解决了表的数据存储和实际需要之间的矛盾。

针对表旋转技术从SQL Server2005开始 系统提供两个新的关键字 PIVOT 和UNPIVOT可用来作表旋转操作.因为这是从SQL Server2005开始提供的新关键字，所以如果希望使用此功能需要将数据库的兼容级别设置为90。

PIVOT用于将列值旋转为列名（即行转列）、UNPIVOT用于将列明转为列值（即列转行）

PIVOT语法规则：

第一部分 SELECT .... FROM 之間:

<non-pivoted column> : 非旋转列。

[first pivoted column] AS <column name> :旋转列 AS <列名>

[first pivoted column] 的名称将会对应与之後第三部分的 FOR column IN 里面的的名称。

第二部分 FROM ..... PIVOT 之間:

(<SELECT query that produces the data>) AS <alias for the source query> : 這裡也就是你的资料来源的地方，要注意的是尾巴AS命名的名称不可以漏掉一定要命名。

第三部分 PIVOT 跟 FOR:

<aggregation function>(<column being aggregated>) :

<聚合函数>（<被聚合的列>）。

[<column that contains the values that will become column headers>] : [<包含将被转换为列标头的值的列>]

IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) : 这部分前面有提到，这边的列名称对应的是第一部分的名称

UNPIVOT语法规则：

第一部分 SELECT .... FROM 之間:

<non-pivoted Rows> : 非旋转行。

[colgroup_column name] :列集合的列名称将会对应与之後第三部分的 FOR column IN 里面的的名称而该列中的数据为IN后面的列名

[value set_column name]值集合的列名的名称将会对应与之後第三部分的 FOR 之前的的名称，值集合数据是根据列分组组成集合！

第二部分 FROM ..... UNPIVOT 之間:

Table Source:表源

第三部分 UNPIVOT 跟 FOR:

[value set_column name] 值集合的列名这部分前面有提到，这边的数据来源为根据[colgroup_column name]根据对应列名分组的数据

[colgroup_column name] 列集合的列名这部分前面有提到，这边的数据来源为根据 IN关键字从Table Source表数据源中获取的列的名称

IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) :这边的列名称对应的是Table Source中列的名称也是

典型实例

一、行转列

建立一个产品销售表，内容包括员工姓名、销售日期、销售金额

代码如下：

Create table Orders

(Staff_name  char(20) not null,

OrderDate  char(50)not null,

Amount    money  notnull

)

insert into Orders values(‘张三‘,‘Jan‘,150.00)

insert into Orders values(‘张三‘,‘Feb‘,100.00)

insert into Orders values(‘张三‘,‘Mar‘,100.00)

insert into Orders values(‘张三‘,‘Apr‘,150.00)

insert into Orders values(‘张三‘,‘May‘,100.00)

insert into Orders values(‘张三‘,‘Jun‘,170.00)

insert into Orders values(‘张三‘,‘Jul‘,100.00)

insert into Orders values(‘张三‘,‘Aug‘,110.00)

insert into Orders values(‘张三‘,‘Sep‘,100.00)

insert into Orders values(‘张三‘,‘Oct‘,180.00)

insert into Orders values(‘张三‘,‘Now‘,100.00)

insert into Orders values(‘张三‘,‘Dec‘,100.00)

 

select Staff_name,[Jan],[Feb],[Mar],[Apr] ,[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Now],[Dec]

from(select Staff_name,OrderDate,Amount from orders) ct

pivot

(

sum(Amount)

for OrderDate in( [Jan],[Feb],[Mar],[Apr] ,[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Now],[Dec])

 

)as Pvt

order by Staff_name

注：使用pivot把一列拆成几列时需要后面as取个别名这是固定的格式同时如 for前是必须使用聚合函数的

当然不使用pivot函数也可以得到相同效果只是代码长切效率低但容易理解

代码如下：

select Staff_name,

sum(case when OrderDate=’Jan’ THEN Amount END) as [Jan],

sum(case when OrderDate=’Feb’ THEN Amount END) as [Feb],

sum(case when OrderDate=’Mar’ THEN Amount END) as [Mar],

sum(case when OrderDate=’Apr’ THEN Amount END) as [Jan],

sum(case when OrderDate=’May’ THEN Amount END) as [May],

sum(case when OrderDate=’Jun’ THEN Amount END) as [Jun],

sum(case when OrderDate=’Jul’ THEN Amount END) as [Jul],

sum(case when OrderDate=’Aug’ THEN Amount END) as [Aug],

sum(case when OrderDate=’Sep’ THEN Amount END) as [Sep],

sum(case when OrderDate=’Oct’ THEN Amount END) as [Oct],

sum(case when OrderDate=’Now’ THEN Amount END) as [Now],

sum(case when OrderDate=’Dec’ THEN Amount END) as [Dec]
from orders as a
group by Staff_name

转换后结果：

二、列转行

沿用上面已经转换好的表结构，其效果如下：

代码如下：

select Staff_name ,OrderDate,Amount from orders2
unpivot
(
Amount for OrderDate in( [Jan],[Feb],[Mar],[Apr] ,[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Now],[Dec])

)as UnPvt

注：同样需要使用as取别名同样是固定的格式 unpivot函数中没有聚合函数 OrderDate和Amount列都是原来没有的 Amount列由原来的[Jan],[Feb],[Mar],[Apr] ,[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Now],[Dec])列组成

请注意，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合，从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果，因为行已经被合并了。另外，UNPIVOT 的输入中的 NULL 不会显示在输出中，然而在执行 PIVOT 操作之前输入中可能会含有原始的 NULL 值。

同样的不使用unpivot也可以实现以上的功能

代码如下：

select Staff_name, ‘Jan‘ as OrderDate,Jan as Amount from orders

union all

select Staff_name, ‘Feb‘ as OrderDate,Feb as Amount from orders

union all

select Staff_name, ‘Mar‘ as OrderDate,Mar as Amount from orders

union all

select Staff_name, ‘Apr‘ as OrderDate,Apr as Amount from orders

union all

select Staff_name, ‘May‘ as OrderDate,May as Amount from orders

union all

select Staff_name, ‘Jun‘ as OrderDate,Jun as Amount from orders

union all

select Staff_name, ‘Jul‘ as OrderDate ,Jul as Amount from orders

union all

select Staff_name, ‘Aug‘ as OrderDate,Aug as Amount from orders

union all

select Staff_name, ‘Sep‘ as OrderDate,Sep as Amount from orders

union all

select Staff_name, ‘Oct‘ as OrderDate ,Oct as Amount from orders

union all

select Staff_name, ‘Now‘ as OrderDate ,Now as Amount from orders

union all

select Staff_name, ‘Dec‘ as OrderDate ,Dec as Amount from orders