Cacher is the code snippet organizer for pro developers

SELECT MAKER, [PC], [LAPTOP], [PRINTER] FROM PRODUCT 
PIVOT 
( COUNT(MODEL) FOR TYPE IN ( [PC], [LAPTOP], [PRINTER] ) ) M


### pivot changes rows into columns
### unpivot changes columns into rows

#### note pivot is not simple transpose
#### pivoting is taking different levels in a variable and converting them to columns.

PIVOTING 
###step1    select column names  #### (for output table) 
###step2    from (table) A       #### data to be pivoted
###step3    PIVOT ( aggregatefn(column3) for column1  in listofvaluesincolumn1) B 

##column1 is the column from where rows are getting converted 
to new column names

###list of values are the values in the old column being converted 
or the new column names being created
### 3 columns in play. group by on two columns first is column1,
second is the unmentioned column and aggregation on the column3








UNPIVOTING 
###step1    select columnname1,columnname2  #### (for output table) 
###step2     from (table) A      ####data to be unpivoted
###step3     UNPIVOT ( columnname1 for columnname2  in tableAcolumnlist) B

##columnname1,columnname2 are both columns of the new output table
## columnname2 will obviously be the column header which will contain 
tableAcolumn list values


####EXAMPLE


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

select * from Product;



select Cust,VEG,SODA,MILK,BEER,CHIPS
into #table1
from Product

PIVOT


(max(qty) for Product in (VEG,SODA,MILK,BEER,CHIPS) )B


select * from #table1


select Cust,Food,value


from #table1


unpivot


(value for food in (VEG,SODA,MILK,BEER,CHIPS))M1