PIVOT and UNPIVOT Table Examples - Sql Server

 PIVOT and UNPIVOT Table Examples - Sql Server

 USE AdventureWorks  
 GO  
 -- Creating Test Table  
 CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)  
 GO  
 -- Inserting Data into Table  
 INSERT INTO Product(Cust, Product, QTY)  
 VALUES('KATE','VEG',2)  
 INSERT INTO Product(Cust, Product, QTY)  
 VALUES('KATE','SODA',6)  
 INSERT INTO Product(Cust, Product, QTY)  
 VALUES('KATE','MILK',1)  
 INSERT INTO Product(Cust, Product, QTY)  
 VALUES('KATE','BEER',12)  
 INSERT INTO Product(Cust, Product, QTY)  
 VALUES('FRED','MILK',3)  
 INSERT INTO Product(Cust, Product, QTY)  
 VALUES('FRED','BEER',24)  
 INSERT INTO Product(Cust, Product, QTY)  
 VALUES('KATE','VEG',3)  
 GO  

 -- Selecting and checking entires in table  
 SELECT *  
 FROM Product  
 GO  
 -- Pivot Table ordered by PRODUCT  
 SELECT PRODUCT, FRED, KATE  
 FROM (  
 SELECT CUST, PRODUCT, QTY  
 FROM Product) up  
 PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt  
 ORDER BY PRODUCT  
 GO  
 -- Pivot Table ordered by CUST  
 SELECT CUST, VEG, SODA, MILK, BEER, CHIPS  
 FROM (  
 SELECT CUST, PRODUCT, QTY  
 FROM Product) up  
 PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt  
 ORDER BY CUST  
 GO  

 -- Unpivot Table ordered by CUST  
 SELECT CUST, PRODUCT, QTY  
 FROM  
 (  
 SELECT CUST, VEG, SODA, MILK, BEER, CHIPS  
 FROM (  
 SELECT CUST, PRODUCT, QTY  
 FROM Product) up  
 PIVOT  
 ( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p  
 UNPIVOT  
 (QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)  
 ) AS Unpvt  
 GO  
 -- Clean up database  
 DROP TABLE Product  
 GO  

 ResultSet:   -- Selecting and checking entires in table  
 Cust Product QTY  
 ------------------------- -------------------- -----------  
 KATE VEG 2  
 KATE SODA 6  
 KATE MILK 1  
 KATE BEER 12  
 FRED MILK 3  
 FRED BEER 24  
 KATE VEG 3  

 -- Pivot Table ordered by PRODUCT  

 PRODUCT FRED KATE  
 -------------------- ----------- -----------  
 BEER 24 12  
 MILK 3 1  
 SODA NULL 6  
 VEG NULL 5  

 -- Pivot Table ordered by CUST  
 CUST VEG SODA MILK BEER CHIPS  
 ------------------------- ----------- ----------- ----------- ----------- -----------  
 FRED NULL NULL 3 24 NULL  
 KATE 5 6 1 12 NULL  

 -- Unpivot Table ordered by CUST  
 CUST PRODUCT QTY  
 ------------------------- -------- -----------  
 FRED MILK 3  
 FRED BEER 24  
 KATE VEG 5  
 KATE SODA 6  
 KATE MILK 1  
 KATE BEER 12 12