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
I need a result set of the following
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
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).