Pivot using SQL Server 2000

Question

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

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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

View more solutions

Comments

[sykespro]

Thanks GregD but Im afraid this wont help me. The problem is that when there is a new record added to the ProductMeta table I will have to go back and alter this statement. Im trying to keep my maintenance points as low as possible.

[GregD]

Ah..Ok Then you're probably going to want to take a look at this article: oreillynet.com/pub/a/network/2004/12/17/crosstab.html

[M.R.]

This only works when the values are static - i.e., you know ahead of time the column values..

[Kirk]

I just reworked a terribly slow pivot query to use the method here with min() and improved the query to execute in under 2 seconds. I award you +2 internets!

[user786]

one thing i dont get it is if you need to use [m].metavalue in all switch cases then why bother using switch case, can u explain a little bit.

[Rich Andrews]

Because, for example, you only want the sum of [m].metavalue where [m].metakey is 'a' to appear in column a. Essentially it filters out out all other metavalue results of the sum. In this way you can transpose the data in the original rows into columns.