SQL Server 2005 Beta 2 Transact-SQL功能（3）

领测软件测试网

　　 
　　在开放架构方案中，您需要用事先不知道或因实体类型而异的属性集来维护实体。应用程序的用户动态定义这些属性。您将属性拆分到不同的行中，并且只为每个实体实例存储相关的属性，而不是在表中预定义很多列并存储很多空值。
　　

　　PIVOT 使您可以为开放架构和其他需要将行旋转为列的方案生成交叉分析报表，并且可能同时计算聚合并且以有用的形式呈现数据。
　　
　　开放架构方案的一个示例是跟踪可供拍卖的项目的数据库。某些属性与所有拍卖项目有关，例如，项目类型、项目的制造日期以及它的初始价格。只有与所有项目有关的属性被存储在 AuctionItems 表中：
　　
　　CREATE TABLE AuctionItems
　　(
　　　itemid　　　 INT　　　　　NOT NULL PRIMARY KEY NONCLUSTERED,
　　　itemtype　　 NVARCHAR(30) NOT NULL,
　　　whenmade　　 INT　　　　　NOT NULL,
　　　initialprice MONEY　　　　NOT NULL,
　　　/* other columns */
　　)
　　CREATE UNIQUE CLUSTERED INDEX idx_uc_itemtype_itemid
　　　ON AuctionItems(itemtype, itemid)
　　INSERT INTO AuctionItems VALUES(1, N'Wine',　　 1822,　　　3000)
　　INSERT INTO AuctionItems VALUES(2, N'Wine',　　 1807,　　　 500)
　　INSERT INTO AuctionItems VALUES(3, N'Chair',　　1753,　　800000)
　　INSERT INTO AuctionItems VALUES(4, N'Ring',　　 -501,　 1000000)
　　INSERT INTO AuctionItems VALUES(5, N'Painting', 1873,　 8000000)
　　INSERT INTO AuctionItems VALUES(6, N'Painting', 1889,　 8000000)
　　
　　其他属性特定于项目类型，并且不同类型的新项目被不断地添加。这样的属性可以存储在不同的 ItemAttributes 表中，其中每个项属性都存储在不同的行中。每个行都包含项目 ID、属性名称和属性值：
　　
　　CREATE TABLE ItemAttributes
　　(
　　　itemid　　INT　　　　　NOT NULL REFERENCES AuctionItems,
　　
　　　attribute NVARCHAR(30) NOT NULL,
　　　value　　 SQL_VARIANT　NOT NULL,
　　　PRIMARY KEY (itemid, attribute)
　　)
　　INSERT INTO ItemAttributes
　　　VALUES(1, N'manufacturer', CAST(N'ABC'　　　　　　　AS NVARCHAR(30)))
　　INSERT INTO ItemAttributes
　　　VALUES(1, N'type',　　　　 CAST(N'Pinot Noir'　　　 AS NVARCHAR(15)))
　　INSERT INTO ItemAttributes
　　　VALUES(1, N'color',　　　　CAST(N'Red'　　　　　　　AS NVARCHAR(15)))
　　INSERT INTO ItemAttributes
　　　VALUES(2, N'manufacturer', CAST(N'XYZ'　　　　　　　AS NVARCHAR(30)))
　　INSERT INTO ItemAttributes
　　　VALUES(2, N'type',　　　　 CAST(N'Porto'　　　　　　AS NVARCHAR(15)))
　　INSERT INTO ItemAttributes
　　　VALUES(2, N'color',　　　　CAST(N'Red'　　　　　　　AS NVARCHAR(15)))
　　INSERT INTO ItemAttributes
　　　VALUES(3, N'material',　　 CAST(N'Wood'　　　　　　 AS NVARCHAR(15)))
　　INSERT INTO ItemAttributes
　　　VALUES(3, N'padding',　　　CAST(N'Silk'　　　　　　 AS NVARCHAR(15)))
　　INSERT INTO ItemAttributes
　　　VALUES(4, N'material',　　 CAST(N'Gold'　　　　　　 AS NVARCHAR(15)))
　　INSERT INTO ItemAttributes
　　　VALUES(4, N'inscription',　CAST(N'One ring ...'　　 AS NVARCHAR(50)))
　　INSERT INTO ItemAttributes
　　　VALUES(4, N'size',　　　　 CAST(10　　　　　　　　　AS INT))
　　INSERT INTO ItemAttributes
　　　VALUES(5, N'artist',　　　 CAST(N'Claude M.net'　　 AS NVARCHAR(30)))
　　INSERT INTO ItemAttributes
　　　VALUES(5, N'name',　　　　 CAST(N'Field of Poppies' AS NVARCHAR(30)))
　　INSERT INTO ItemAttributes
　　　VALUES(5, N'type',　　　　 CAST(N'Oil'　　　　　　　AS NVARCHAR(30)))
　　INSERT INTO ItemAttributes
　　　VALUES(5, N'height',　　　 CAST(19.625　　　　　　　AS NUMERIC(9,3)))
　　INSERT INTO ItemAttributes
　　　VALUES(5, N'width',　　　　CAST(25.625　　　　　　　AS NUMERIC(9,3)))
　　INSERT INTO ItemAttributes
　　　VALUES(6, N'artist',　　　 CAST(N'Vincent Van Gogh' AS NVARCHAR(30)))
　　INSERT INTO ItemAttributes
　　　VALUES(6, N'name',　　　　 CAST(N'The Starry Night' AS NVARCHAR(30)))
　　INSERT INTO ItemAttributes
　　　VALUES(6, N'type',　　　　 CAST(N'Oil'　　　　　　　AS NVARCHAR(30)))
　　INSERT INTO ItemAttributes
　　　VALUES(6, N'height',　　　 CAST(28.75　　　　　　　 AS NUMERIC(9,3)))
　　INSERT INTO ItemAttributes
　　　VALUES(6, N'width',　　　　CAST(36.25　　　　　　　 AS NUMERIC(9,3)))
　　
　　请注意，sql_variant 数据类型被用于 value 列，因为不同的属性值可能具有不同的数据类型。例如，size 属性存储整数属性值，而 name 属性存储字符串属性值。
　　
　　假设您希望呈现 ItemAttributes 表中的数据，该表具有与每个油画项目（项目 5、6）相对应的行以及与每个属性相对应的列。如果没有 PIVOT 运算符，则必须编写如下所示的查询：
　　
　　SELECT
　　　itemid,
　　　MAX(CASE WHEN attribute = 'artist'　THEN value END) AS [artist],
　　　MAX(CASE WHEN attribute = 'name'　　THEN value END) AS [name],
　　　MAX(CASE WHEN attribute = 'type'　　THEN value END) AS [type],
　　　MAX(CASE WHEN attribute = 'height'　THEN value END) AS [height],
　　　MAX(CASE WHEN attribute = 'width'　 THEN value END) AS [width]
　　FROM ItemAttributes AS ATR
　　WHERE itemid IN(5,6)
　　GROUP BY itemid
　　
　　以下为结果集：
　　
　　itemid artist　　　　　 name　　　　　　 type　　　 height width
　　------ ---------------- ---------------- ---------- ------ ------
　　5　　　Claude Monet　　 Field of Poppies Oil　　　　19.625 25.625
　　6　　　Vincent Van Gogh The Starry Night Oil　　　　28.750 36.250
　　
　　PIVOT 运算符使您可以维护更简短且更可读的代码以获得相同的结果：
　　
　　SELECT *
　　FROM ItemAttributes AS ATR
　　　PIVOT
　　
　　　(
　　　　MAX(value)
　　　　FOR attribute IN([artist], [name], [type], [height], [width])
　　　) AS PVT
　　WHERE itemid IN(5,6)
　　
　　像大多数新功能一样，对 PIVOT 运算符的理解来自于试验和使用。PIVOT 语法中的某些元素是显而易见的，并且只需要您弄清楚这些元素与不使用新运算符的查询之间的关系。其他元素则是隐藏的。
　　
　　您可能会发现下列术语能够帮助您理解 PIVOT 运算符的语义：
　　
　　table_expression
　　
　　PIVOT 运算符所作用于的虚拟表（查询中位于 FROM 子句和 PIVOT 运算符之间的部分）：在该示例中为 ItemAttributes AS ATR。
　　
　　pivot_column
　　
　　table_expression 中您希望将其值旋转为结果列的列：在该示例中为 attribute。
　　
　　column_list
　　
　　pivot_column 中您希望将其呈现为结果列的值列表（在 IN 子句前面的括号中）。它们必须表示为合法的标识符：在该示例中为 [artist]、[name]、[type]、[height]、[width]。
　　
　　aggregate_function
　　
　　用于生成结果中的数据或列值的聚合函数：在该示例中为 MAX()。
　　
　　value_column
　　
　　table_expression 中的用作 aggregate_function 的参数的列：在该示例中为 value。
　　
　　group_by_list
　　
　　隐藏的部分 — table_expression 中除 pivot_column 和 value_column 以外所有用来对结果进行分组的列：在该示例中为 itemid。
　　
　　select_list
　　
　　SELECT 子句后面的列列表，可能包括 group_by_list 和 column_list 中的任何列。别名可以用来更改结果列的名称：* 在该示例中，返回 group_by_list 和 column_list 中的所有列。
　　
　　PIVOT 运算符为 group_by_list 中的每个唯一值返回一个行，就好像您的查询带有 GROUP BY 子句并且您指定了这些列一样。请注意，group_by_list 是隐含的；它没有在查询中的任何位置显式指定。它包含 table_expression 中除 pivot_column 和 value_column 以外的所有列。理解这一点可能是理解您用 PIVOT 运算符编写的查询按照它们本身的方式工作以及在某些情况下可能获得错误的原因的关键。
　　
　　可能的结果列包括 group_by_list 和 中的值。如果您指定星号 (*)，则查询会返回这两个列表。结果列的数据部分或结果列值是通过将 value_column 用作参数的 aggregate_function 计算的。
　　
　　下面的用各种颜色突出显示的代码说明了使用 PIVOT 运算符的查询中的不同元素：
　　
　　SELECT * -- itemid, [artist], [name], [type], [height], [width]
　　FROM ItemAttributes AS ATR
　　　PIVOT
　　　(
　　　　MAX(value)
　　　　FOR attribute IN([artist], [name], [type], [height], [width])
　　　) AS PVT
　　WHERE itemid IN(5,6)
　　
　　以下代码将不同的元素与不使用 PIVOT 运算符的查询相

文章来源于领测软件测试网 https://www.ltesting.net/