使用 SQL Server 2000 进行透视
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/312861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot using SQL Server 2000
提问by sykespro
I put together a sample scenario of my issue and I hope its enough for someone to point me in the right direction.
我整理了我的问题的示例场景，我希望它足以让有人指出我正确的方向。
I have two tables
我有两张桌子
Products
产品
Product Meta
产品元
I need a result set of the following
我需要以下结果集
采纳答案by Rich Andrews
We've successfully used the following approach in the past...
我们过去成功地使用了以下方法......
SELECT [p].ProductID,
[p].Name,
MAX(CASE [m].MetaKey
WHEN 'A'
THEN [m].MetaValue
END) AS A,
MAX(CASE [m].MetaKey
WHEN 'B'
THEN [m].MetaValue
END) AS B,
MAX(CASE [m].MetaKey
WHEN 'C'
THEN [m].MetaValue
END) AS C
FROM Products [p]
INNER JOIN ProductMeta [m]
ON [p].ProductId = [m].ProductId
GROUP BY [p].ProductID,
[p].Name
It can also be useful transposing aggregations with the use of...
使用...转置聚合也很有用。
SUM(CASE x WHEN 'y' THEN yVal ELSE 0 END) AS SUMYVal
EDIT
编辑
Also worth noting this is using ANSI standard SQL and so it will work across platforms :)
还值得注意的是，这是使用 ANSI 标准 SQL，因此它可以跨平台工作:)
回答by Thomas
I realize this is two years old, but it bugs me that the accepted answer calls for using dynamic SQL and the most upvoted answer won't work:
我意识到这已经两年了，但让我感到困扰的是，接受的答案要求使用动态 SQL，而最受好评的答案将不起作用：
Select P.ProductId, P.Name
, Min( Case When PM.MetaKey = 'A' Then PM.MetaValue End ) As A
, Min( Case When PM.MetaKey = 'B' Then PM.MetaValue End ) As B
, Min( Case When PM.MetaKey = 'C' Then PM.MetaValue End ) As C
From Products As P
Join ProductMeta As PM
On PM.ProductId = P.ProductId
Group By P.ProductId, P.Name
You mustuse a Group By or you will get a staggered result. If you are using a Group By, you must wrap each column that is not in the Group By clause in an aggregate function (or a subquery).
您必须使用 Group By，否则您将获得交错的结果。如果您使用的是 Group By，则必须将不在 Group By 子句中的每一列包装在聚合函数（或子查询）中。
回答by AaronLS
If your database engine is 2005 and your database is in 2000 compatibility mode, you can work around the lower compatibility mode by running your query from a 2005 database. Target the 2000 database by using 3 part naming convention for your tables in the query such as DatabaseNameHere.dbo.TableNameHere
如果您的数据库引擎是 2005 并且您的数据库处于 2000 兼容模式，则可以通过从 2005 数据库运行查询来解决较低的兼容模式。通过对查询中的表使用 3 部分命名约定来定位 2000 数据库，例如DatabaseNameHere.dbo.TableNameHere
回答by GregD
Select a.ProductId
,a.Name
,(Select c.MetaValue
From [Product Meta] c
Where c.ProductId = a.ProductId
And c.MetaKey = 'A') As 'A'
,(Select d.MetaValue
From [Product Meta] d
Where d.ProductId = a.ProductId
And d.MetaKey = 'B') As 'B'
,(Select e.MetaValue
From [Product Meta] e
Where e.ProductId = a.ProductId
And e.MetaKey = 'C') As 'C'
From Products a
Order By a.ProductId Asc
回答by Mladen Prajdic
use the stored procedure here: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
在此处使用存储过程：http: //www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
also check the comments.
也检查评论。
