ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL Server 2005 交叉表

SQL Server 2005 交叉表

原创 Linux操作系统 作者:irenetongying 时间:2019-03-09 16:57:07 0 删除 编辑

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

注意:

对升级到 Microsoft SQL Server 2005 的数据库使用 PIVOT UNPIVOT 时,该数据库的兼容级别必须设置为 90。若要设置数据库的兼容级别,请参阅 sp_dbcmptlevel (Transact-SQL)


PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单、更具可读性。

常见的可能会用到 PIVOT 的情形是,需要生成交叉表格报表以汇总数据时。例如,假设需要在 AdventureWorks 示例数据库中查询 PurchaseOrderHeader 表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表(按供应商划分):

USE AdventureWorks;

GO

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5

FROM

(SELECT PurchaseOrderID, EmployeeID, VendorID

FROM Purchasing.PurchaseOrderHeader) p

PIVOT

(

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) AS pvt

ORDER BY VendorID

以下为部分结果集:

VendorID Emp1 Emp2 Emp3 Emp4 Emp5

1 4 3 5 4 4

2 4 1 5 5 5

3 4 3 5 4 4

4 4 2 5 5 4

5 5 1 5 5 5

将在 EmployeeID 列上透视此嵌套 select 语句返回的结果。

SELECT PurchaseOrderID, EmployeeID, VendorID

FROM PurchaseOrderHeader

这意味着 EmployeeID 列返回的唯一值自行变成了最终结果集中的字段。结果,在透视子句中指定的每个 EmployeeID 号(在本例中为雇员 164198223231 233)都有相应的一列。PurchaseOrderID 列用作值列,将根据此列对最终输出中返回的列(称为分组列)进行分组。在本例中,通过 COUNT 函数聚合了分组列。请注意,将显示出一条警告消息,指明针对每个雇员计算 COUNT 时不考虑显示在 PurchaseOrderID 列中的任何 NULL 值。

重要事项:

如果 PIVOT 中使用聚合函数,则计算聚合时将不考虑出现在值列中的任何 NULL 值。

UNPIVOT 将与 PIVOT 执行几乎完全相反的操作,将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt,并且您需要将列标识符 Emp1Emp2Emp3Emp4 Emp5 转换为对应于某个特定供应商的行值。这意味着必须标识另外两个列。包含所转换列值(Emp1Emp2...)的列将被称为 Employee,保存当前驻留在所转换列下的值的列将被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_column value_column。该查询如下所示:

--Create the table and insert values as portrayed in the above example.

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

Emp3 int, Emp4 int, Emp5 int)

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4)

INSERT INTO pvt VALUES (2,4,1,5,5,5)

INSERT INTO pvt VALUES (3,4,3,5,4,4)

INSERT INTO pvt VALUES (4,4,2,5,5,4)

INSERT INTO pvt VALUES (5,5,1,5,5,5)

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM

(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

FROM pvt) p

UNPIVOT

(Orders FOR Employee IN

(Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt

GO

以下为部分结果集:

VendorID Employee Orders

1 Emp1 4

1 Emp2 3

1 Emp3 5

1 Emp4 4

1 Emp5 4

2 Emp1 4

2 Emp2 1

2 Emp3 5

2 Emp4 5

2 Emp5 5

...

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/166189/viewspace-113562/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-11-03

  • 博文量
    8
  • 访问量
    5600