What is the exact use of PIVOT in sql server ?

 Posted by Chvrsri on 12/9/2010 | Category: Sql Server Interview questions | Views: 4837 | Points: 40
Answer:

The use of Pivot element is , it is used to reorganize and summarize the selected columns and rows of data in a table to produce the desired reports.

For example ,

Let us create a table :

CREATE TABLE ItemSales(

SalesPerson VARCHAR(50),
Item VARCHAR(50),
ItemAmount INT)


Insert values into it :

INSERT INTO ItemSales 

VALUES('Person1', 'Pickles', $100.00)
INSERT INTO ItemSales
VALUES('Person1', 'Pickles', $100.00)
INSERT INTO ItemSales
VALUES('Person2' ,'Oranges' ,$50.00 )
INSERT INTO ItemSales
VALUES('Person2', 'Pickles', $25.00)
INSERT INTO ItemSales
VALUES('Person2', 'Oranges', $300.00)
INSERT INTO ItemSales
VALUES('Person1', 'Oranges', $500.00)


Now create and assigning a pivot element

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles

FROM
(SELECT SalesPerson, Item, ItemAmount
FROM ItemSales ) ps
PIVOT
(
SUM (ItemAmount)
FOR Item IN
( [Oranges], [Pickles])
) AS Pvt



After this the complete data is summarized and the report generated is in this format:

-----------------------------------------------
SalesPerson || Oranges || Pickles
-----------------------------------------------
Person1 || 500 || 200
Person2 || 350 || 25


Source: My Own Observation | Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Chvrsri on: 12/9/2010 | Points: 10
Hi pandian,

I apology for my mistake earlier.I hope i am correct now.I didn't receive any notification that you posted a question to my post. Any how thank you so much for letting my mistake known.
Posted by: PandianS on: 12/9/2010 | Points: 10
Hi

I really appreciate and thanks

Cheers

Login to post response