What is the exact use of PIVOT in sql server ?

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