当前位置：文江博客话题详情

使用 SQL Server 2000 进行数据透视

发布于 2024-07-09 22:46:51 字数 292 浏览 4 评论 0原文

我整理了我的问题的示例场景，希望有人能指出正确的方向。

我有两个表

产品

alt text

产品元

alt text

我需要以下结果集

alt text

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

若水微香 2024-07-16 22:46:51

我意识到这已经有两年了，但让我感到困扰的是，接受的答案要求使用动态 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

你必须使用分组依据，否则你会得到一个交错的结果结果。如果使用 Group By，则必须将不在 Group By 子句中的每一列包装在聚合函数（或子查询）中。

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:

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 must use 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).

回复收藏 0 原文

与酒说心事 2024-07-16 22:46:51

我们过去已经成功地使用了以下方法...

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

它也可以通过使用...

SUM(CASE x WHEN 'y' THEN yVal ELSE 0 END) AS SUMYVal

编辑

进行转置聚合，另外值得注意的是，这是使用 ANSI 标准 SQL，因此它可以工作跨平台:)

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 :)

回复收藏 0 原文

屌丝范 2024-07-16 22:46:51

如果您的数据库引擎是 2005 并且数据库处于 2000 兼容模式，则可以通过从 2005 数据库运行查询来解决较低的兼容性模式。通过对查询中的表使用 3 部分命名约定来定位 2000 数据库，例如 DatabaseNameHere.dbo.TableNameHere

回复收藏 0 原文

说谎友 2024-07-16 22:46:51

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

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

回复收藏 0 原文

~没有更多了~