Pivot using SQL Server 2000

I need some urgent help! 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

Answers


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


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


Need Your Help

How to copy files using Ant between two computers with a low speed of connection?

java ant build copy

I had two computers that are located in different parts of the world (Russia and USA) and I need to copy many (100+) small files from one machine to another with Ant. I use usual approach:

Is it secure to upload an application on VPS Hosting Companies?

java security hosting vps

This is the first time I've to deploy an application on a VPS host somewhere around the globe.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.