枢纽表达式PIVOT和UNPIVOT的简单用法

一：前言

最近在论坛上面看到一些提问涉及到行列的旋转，想到SQL Server 2005新增的枢纽表达式正好可以实现这样的用法。

 

二：可行性分析

PIVOT 和 UNPIVOT 是可以在查询的 FROM 子句中指定的新的关系运算符。它们对一个输入表值表达式执行某种操作，并且产生一个输出表作为结果。PIVOT 运算符将行旋转为列，并且可能同时执行聚合。它基于给定的枢轴列扩大输入表表达式，并生成一个带有与枢轴列中的每个唯一值相对应的列的输出表。UNPIVOT 运算符执行与 PIVOT 运算符相反的操作；它将列旋转为行。它基于枢轴列收缩输入表表达式。

 

三：语法

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>) 

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR 

[<column that contains the values that will become column headers>] 

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

 

三：实例

1. 建立出货数量表，有两个栏位出货的日期和出货的数量

CREATE TABLE SHIP 
( 
SHPDAT VARCHAR(12), 
SHPQTY INT 
) 

INSERT INTO SHIP VALUES 
('20100717',230), 
('20100718',100), 
('20100719',150), 
('20100720',200), 
('20100721',260) 

 

 

2. 用PIVOT把行转换为列 

SELECT [20100717],[20100718],[20100719],[20100720],[20100721],[20100722],[20100723] FROM SHIP 
PIVOT 
( 
SUM(SHPQTY) 
FOR SHPDAT IN ([20100717],[20100718],[20100719],[20100720],[20100721],[20100722],[20100723]) 
)AS PVT

 

原始表中的行为转换为列，看起来好像是以SUM(SHPQTY)为枢纽做旋转。

注：如果聚合函数与 PIVOT 一起使用，则计算聚合时将不考虑出现在值列中的任何空值。

 

3. 把用PIVOT转换过的数据塞入测试表SHIP2

SELECT [20100717],[20100718],[20100719],[20100720],[20100721],[20100722],[20100723] into SHIP2 FROM SHIP 
PIVOT 
( 
SUM(SHPQTY) 
FOR SHPDAT IN ([20100717],[20100718],[20100719],[20100720],[20100721],[20100722],[20100723]) 
)AS PVT 

 

4.用UNPIVOT把列转换为行

SELECT SHPDAT,SHPQTY FROM SHIP2 
UNPIVOT 
( 
SHPQTY 
FOR SHPDAT IN ([20100717],[20100718],[20100719],[20100720],[20100721],[20100722],[20100723]) 
)AS UNPVT

  

列转换为行值，SHPDAT,SHPQTY是新命名的两列名。

注：UNPIVOT 的输入中的空值不会显示在输出中。

 

四：总结

可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。