Pivot using SQL Server 2000

Answer 1

use the stored procedure here: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

also check the comments.

Answer 2

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

Answer 3

We've successfully used the following approach in the past...

SELECT [p].ProductID, [p].Name,
CASE [m].MetaKey WHEN 'A' THEN [m].MetaValue END AS A,
CASE [m].MetaKey WHEN 'B' THEN [m].MetaValue END AS B,
CASE [m].MetaKey WHEN 'C' THEN [m].MetaValue END AS C
FROM Products [p]
INNER JOIN ProductMeta [m]
ON [p].ProductId = [m].ProductId

It can also be useful transposing aggregations with the use of...

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

Answer 4

Yet another SQL Cross Tab proc http://johnmacintyre.ca/codespct.asp

Try not to laugh at my site ... it's been a heck of a long time since I've updated it. ;-)

Answer 5

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