Rows in their own columns depending on their value

Question

I have a selection request that currently gives the following results:

Description   Code    Price
 Product 1     A        5
 Product 1     B        4
 Product 1     C        2

Using the following query:

SELECT DISTINCT np.Description, p.promotionalCode, p.Price
FROM            Price AS p INNER JOIN
                         nProduct AS np ON p.nProduct = np.Id

I want to create the following:

Description  A  B  C
 Product 1   5  4  2

Answer 1

SELECT 
    np.Id, 
    np.Description, 
    MIN(Case promotionalCode WHEN 'A' THEN Price ELSE NULL END) AS 'A',
    MIN(Case promotionalCode WHEN 'B' THEN Price ELSE NULL END) AS 'B',
    MIN(Case promotionalCode WHEN 'C' THEN Price ELSE NULL END) AS 'C'
FROM 
    Price AS p 
INNER JOIN nProduct AS np ON p.nProduct = np.Id
GROUP BY 
    np.Id,
    np.Description

---

Here is a simple example:

DECLARE @temp TABLE (
    id INT,
    description varchar(50),
    promotionalCode char(1),
    Price smallmoney
)

INSERT INTO @temp
select 1, 'Product 1', 'A', 5
    union
SELECT 1, 'Product 1',  'B', 4
    union
SELECT 1, 'Product 1', 'C', 2



SELECT
    id,
    description,
    MIN(Case promotionalCode WHEN 'A' THEN Price ELSE NULL END) AS 'A',
    MIN(Case promotionalCode WHEN 'B' THEN Price ELSE NULL END) AS 'B',
    MIN(Case promotionalCode WHEN 'C' THEN Price ELSE NULL END) AS 'C'
FROM
     @temp
GROUP BY 
    id,
    description

Answer 2

If you are using SQL Server 2005, you can use the new PIVOT statement.

Simple PIVOT - the number of orders a customer places for individual products.

The structure of a simple order table:

CREATE TABLE Sales.[Order]
    (Customer varchar(8), Product varchar(5), Quantity int)

The table contains the following values:

Customer Product Quantity
    Mike     Bike    3
    Mike     Chain   2
    Mike     Bike    5
    Lisa     Bike    3
    Lisa     Chain   3
    Lisa     Chain   4

Example: PIVOT operation in the order table:

SELECT *
    FROM Sales.[Order]
    PIVOT (SUM(Quantity) FOR Product IN ([Bike],[Chain])) AS PVT

The expected result of this query is:

Customer Bike Chain
Lisa        3    7
Mike        8    2

If you are not using SQL Server, you can find a “vault” for your database.

Answer 3

. , AVG SUM MIN, , .

If your DBMS supports it, you can also view the Crosstab query or summary query. For example, MS Access has crosstab queries.