Pivot & UnPivot Samples

SELECT SalesPerson
        , ProductType
        , Qty
        , DATE
    FROM Product

 
SalesPerson    ProductType    Qty    DATE
AA    Bike    54    2010-01-01
AA    Car    59    2010-01-02
BB    Bike    572    2010-01-03
BB    Car    42    2010-01-01
BB    Bike    53    2010-01-02

 
SELECT SalesPerson ,  SUM([Bike]) AS Bike , SUM([Car]) AS Car
    FROM Product
    PIVOT
        (
SUM(QTY)
        FOR ProductType IN
([Bike],[Car])
        )
AS PivotTable
    GROUP
BY SalesPerson

 
SalesPerson    Bike    Car
AA    54    59
BB    625    42
 
OR
SELECT SalesPerson, CASE WHEN ProductType = 'Bike' THEN SUM(Qty) 

        ELSE 0 END
AS Bike,
CASE WHEN ProductType = 'Car' THEN SUM(Qty)

ELSE 0 END AS Car
        FROM Product
        GROUP BY SalesPerson, ProductType

 
SalesPerson    Bike    Car
AA    54    0
BB    625    0
AA    0    59
BB    0    42

 
UNPIVOT
 
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
 
VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
1492        2           5           4           4           4
1494        2           5           4           5           4
1496        2           4           4           5           5
1498        2           5           4           4           4
1500        3           4           4           5           4
 
SELECT VendorId, Employee, Orders
FROM ( SELECT VendorId, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p
UNPIVOT
( Orders FOR Employee IN
(Emp1, Emp2)
)
AS unpvt;

 
VendorID   Employee   Orders
---------- ---------- ------
1          Emp1       4
1          Emp2       3
1          Emp3       5
1          Emp4       4
1          Emp5       4

2          Emp1       4