How to Pivot Data in SQL Server 2000 Using Conditional Aggregation?

Pivoting Data in SQL Server 2000

Pivoting data refers to the process of transforming a dataset from a vertical, column-oriented format to a horizontal, row-oriented format. In other words, it allows you to rotate the data in a table so that columns become rows and vice versa.

Consider the following two tables:

• Products

  ProductId	Name
  1	Product A
  2	Product B

• Product Meta

  ProductId	MetaKey	MetaValue
  1	A	Value A for Product A
  1	B	Value B for Product A
  1	C	Value C for Product A
  2	A	Value A for Product B
  2	B	Value B for Product B
  2	C	Value C for Product B

You want to obtain a result set that displays the product names as rows and the meta keys as columns, with the corresponding meta values in each cell:

ProductName	Meta A	Meta B	Meta C
Product A	Value A for Product A	Value B for Product A	Value C for Product A
Product B	Value A for Product B	Value B for Product B	Value C for Product B

To achieve this pivot, you can use a combination of conditional aggregation and the CASE statement within a SELECT statement:

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

This query will correctly group the rows by product ID and name and retrieve the desired meta values for each key.

The above is the detailed content of How to Pivot Data in SQL Server 2000 Using Conditional Aggregation?. For more information, please follow other related articles on the PHP Chinese website!