Using PIVOT and UNPIVOT in sql

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Ex. Table Pivot_Test has data given below

Sales Person	Product	Sales Amount
Bob	Pickles	100
Sue	Oranges	50
Bob	Pickles	25
Bob	Oranges	300
Sue	Oranges	500
Due	Mangoes	800

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM Pivot_Test ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

Output

Sales Person	Oranges	Pickles
Bob	300	125
Due	NULL	NULL
Sue	550	NULL

So how does this work?
There are three pieces that need to be understood in order to construct the query.
1. The SELECT statement
    SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
    This portion of the query selects the three columns for the final result set (SalesPerson, Oranges,      Pickles)
2. The query that pulls the raw data to be prepared
    (SELECT SalesPerson, Product, SalesAmount FROM Pivot_Test) ps
    This query pulls all the rows of data that we need to create the cross-tab results.  The (ps) after the                  query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
3. The PIVOT expression
    PIVOT (SUM (SalesAmount) FOR Product IN ( [Oranges], [Pickles]) ) AS pvt
    This query does the actual summarization and puts the results into a temporary table called pvt

Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3).  These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.