How to Pivot Product Metadata in SQL Server 2000 using a JOIN and Aggregate Function?

Pivot Transformation using SQL Server 2000

Problem:

Your database contains two tables, Products and Product Meta, with the following structures:

Products:
- ProductId
- Name

Product Meta:
- ProductId
- MetaKey
- MetaValue

You wish to transform this data into a result set that pivots the MetaValue column, based on the MetaKey column. The desired result set is:

ProductId | Name | A | B | C

Solution:

Although the previously suggested solutions may not be optimal, they nonetheless highlight the use of aggregate functions or subqueries to ensure valid grouping. Here's an alternative approach:

SELECT
  P.ProductId,
  P.Name,
  MAX(CASE WHEN PM.MetaKey = 'A' THEN PM.MetaValue END) AS A,
  MAX(CASE WHEN PM.MetaKey = 'B' THEN PM.MetaValue END) AS B,
  MAX(CASE WHEN PM.MetaKey = 'C' THEN PM.MetaValue END) AS C
FROM Products AS P
LEFT JOIN ProductMeta AS PM
  ON PM.ProductId = P.ProductId
GROUP BY
  P.ProductId,
  P.Name;

This query joins the Products and Product Meta tables, grouping the results by ProductId and Name. The aggregate function MAX() is used to extract the maximum MetaValue for each MetaKey. This ensures that only the latest or most recent MetaValue for each key is included in the result set.

The above is the detailed content of How to Pivot Product Metadata in SQL Server 2000 using a JOIN and Aggregate Function?. For more information, please follow other related articles on the PHP Chinese website!