SQL Server 2005 Beta 2 Transact-SQL 增强功能(二)
FROM ItemAttributes AS ATR
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemid IN(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
请注意，您必须显式指定 中的值。PIVOT 运算符没有提供在静态查询中从 pivot_column 动态得到这些值的选项。您可以使用动态 SQL 自行构建查询字符串以达到该目的。
将上一个 PIVOT 查询向前推进一步，假设您希望为每个拍卖项目返回所有与油画相关的属性。您希望包括那些出现在 AuctionItems 中的属性以及那些出现在 ItemAttributes 中的属性。您可能尝试以下查询，它会返回错误：
SELECT *
FROM AuctionItems AS ITM
JOIN ItemAttributes AS ATR
ON ITM.itemid = ATR.itemid
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemtype = ''Painting''
以下为错误消息：
.Net SqlClient Data Provider:Msg 8156, Level 16, State 1, Line 1
The column ''itemid'' was specified multiple times for ''PVT''.
请记住，PIVOT 作用于 table_expression，它是由该查询中 FROM 子句和 PIVOT 子句之间的部分返回的虚拟表。在该查询中，虚拟表包含 itemid 列的两个实例 — 一个源自 AuctionItems，另一个源自 ItemAttributes。您可能会试探按如下方式修改该查询，但是您仍将获得错误：
SELECT ITM.itemid, itemtype, whenmade, initialprice,
[artist], [name], [type], [height], [width]
FROM AuctionItems AS ITM
JOIN ItemAttributes AS ATR
ON ITM.itemid = ATR.itemid
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
WHERE itemtype = ''Painting''
以下为错误消息：
.Net SqlClient Data Provider: Msg 8156, Level 16, State 1, Line 1
The column ''itemid'' was specified multiple times for ''PVT''.
.Net SqlClient Data Provider: Msg 107, Level 15, State 1, Line 1
The column prefix ''ITM'' does not match with a table name or alias name used in the query.
正如前面提到的那样，PIVOT 运算符作用于由 table_expression 返回的虚拟表，而不是作用于 select_list 中的列。select_list 在 PIVOT 运算符执行它的操作之后计算，并且只能引用 group_by_list 和 column_list。这就是在 select_list 中不再识别 ITM 别名的原因。如果您了解这一点，您就会意识到，应当向 PIVOT 提供一个只包含您希望施加作用的列的 table_expression。这包括分组列（只有 itemid 的一个实例，外加 itemtype、whenmade 和 initialprice）、枢轴列 (attribute) 和值列 (value)。您可以通过使用 CTE 或派生表做到这一点。以下是一个使用 CTE 的示例：
WITH PNT
AS
(
SELECT ITM.*, ATR.attribute, ATR.value
FROM AuctionItems AS ITM
JOIN ItemAttributes AS ATR
ON ITM.itemid = ATR.itemid
WHERE ITM.itemtype = ''Painting''
)
SELECT * FROM PNT
PIVOT
(
MAX(value)
FOR attribute IN([artist], [name], [type], [height], [width])
) AS PVT
以下为结果集：
itemid itemtype whenmade initialprice arti