PIVOT TABLE IN SQL SERVER

USE tempdb

-- Creating Test Table

CREATE TABLE ITEM(CUSTOMER VARCHAR(25), Product VARCHAR(20), QTY INT)
--------------------------------------------------------------------------------------------------------------------

-- Inserting Data into Table

INSERT INTO ITEM(CUSTOMER, Product, QTY)
VALUES('RAM','VEG',2)
INSERT INTO ITEM(CUSTOMER, Product, QTY)
VALUES('RAM','VEG',3)
INSERT INTO ITEM(CUSTOMER, Product, QTY)
VALUES('RAM','SODA',6)
INSERT INTO ITEM(CUSTOMER, Product, QTY)
VALUES('RAM','MILK',1)
INSERT INTO ITEM(CUSTOMER, Product, QTY)
VALUES('RAM','BEER',12)
INSERT INTO ITEM(CUSTOMER, Product, QTY)
VALUES('SAM','MILK',3)
INSERT INTO ITEM(CUSTOMER, Product, QTY)
VALUES('SAM','BEER',24)
INSERT INTO ITEM(CUSTOMER, Product, QTY)
VALUES('SAM','VEG',5)

--------------------------------------------------------------------------------------------------------------------
-- Selecting and checking entires in table

SELECT * FROM ITEM
--------------------------------------------------------------------------------------------------------------------
-- Pivot Table ordered by PRODUCT
-- SELECT * FROM ITEM

SELECT PRODUCT, RAM, SAM
FROM (
SELECT CUSTOMER, PRODUCT, QTY
FROM ITEM) up
PIVOT (SUM(QTY) FOR CUSTOMER IN (RAM, SAM)) AS pvt
ORDER BY PRODUCT

--------------------------------------------------------------------------------------------------------------------
-- Pivot Table ordered by CUSTOMER
-- SELECT * FROM ITEM

SELECT CUSTOMER, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUSTOMER, PRODUCT, QTY
FROM ITEM) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUSTOMER

--------------------------------------------------------------------------------------------------------------------
-- Using where, group by & order by
-- SELECT * FROM ITEM

SELECT Product as RAM, sum(qty)as Qty
FROM ITEM
WHERE CUSTOMER='RAM'
GROUP BY Product
order by qty

--------------------------------------------------------------------------------------------------------------------
-- Unpivot Table ordered by CUSTOMER
-- SELECT * FROM ITEM

SELECT CUSTOMER, PRODUCT, QTY
FROM
(
SELECT CUSTOMER, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUSTOMER, PRODUCT, QTY
FROM ITEM) 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

--------------------------------------------------------------------------------------------------------------------
-- Clean up database

DROP TABLE ITEM

---------------------------------------R E S U L T----------------------------------------------------------------

-- Selecting and checking entires in table
CUSTOMER Product QTY
------------------------- -------------------- -----------
RAM VEG 2
RAM SODA 6
RAM MILK 1
RAM BEER 12
SAM MILK 3
SAM BEER 24
RAM VEG 3

-- Pivot Table ordered by PRODUCT
PRODUCT SAM RAM
-------------------- ----------- -----------
BEER 24 12
MILK 3 1
SODA NULL 6
VEG NULL 5

-- Pivot Table ordered by CUSTOMER
CUSTOMER VEG SODA MILK BEER CHIPS
------------------------- ----------- ----------- ----------- ----------- -----------
SAM NULL NULL 3 24 NULL
RAM 5 6 1 12 NULL

-- Using where, group by & order by
RAM Qty
----- -------
MILK 1
VEG 5
SODA 6
BEER 12

-- Unpivot Table ordered by CUSTOMER
CUSTOMER PRODUCT QTY
RAM VEG 2
RAM VEG 3
RAM SODA 6
RAM MILK 1
RAM BEER 12
SAM MILK 3
SAM BEER 24

More Help :- http://vivekjohari.blogspot.in/2012/01/pivot-table-in-sql-server.html