find the following example to use Pivot and UnPivot. I am able to find Pivot but when try to find UnPivot i need to search it from any other link. Now i am going to paste both samples in this example
GO
-- Creating Test Table
CREATE TABLE PivotExm(Category VARCHAR(25), PivotExm VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO PivotExm(Category, PivotExm, QTY)
VALUES('shop','VEG',2)
INSERT INTO PivotExm(Category, PivotExm, QTY)
VALUES('shop','SODA',6)
INSERT INTO PivotExm(Category, PivotExm, QTY)
VALUES('shop','MILK',1)
INSERT INTO PivotExm(Category, PivotExm, QTY)
VALUES('shop','BEER',12)
INSERT INTO PivotExm(Category, PivotExm, QTY)
VALUES('butt','MILK',3)
INSERT INTO PivotExm(Category, PivotExm, QTY)
VALUES('butt','BEER',24)
INSERT INTO PivotExm(Category, PivotExm, QTY)
VALUES('shop','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM PivotExm
GO
-- Pivot Table ordered by PivotExm
SELECT PivotExm, butt, shop
FROM (
SELECT Category, PivotExm, QTY
FROM PivotExm) up
PIVOT (SUM(QTY) FOR Category IN (butt, shop)) AS pvt
ORDER BY PivotExm
GO
-- Unpivot Table ordered by Category
SELECT Category, PivotExm, QTY
FROM
(
SELECT Category, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT Category, PivotExm, QTY
FROM PivotExm) up
PIVOT
( SUM(QTY) FOR PivotExm IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PivotExm IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO
-- Clean up database
DROP TABLE PivotExm
GO
