PIVOT Operator in SQL

PIVOT is a new T-SQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional crosstab query.

How to use Pivot:

In your SELECT statement, you specify the values you want to pivot on. The following example using the AdventureWorks database uses the order years (calculated using the DatePart function) as the columns. The FROM clause looks normal except for the PIVOT statement. This statement creates the value you want to show in the rows of the newly created columns. This example uses the aggregate SUM of TotalDue (a calculated field in the FROM clause). Then we use the FOR operator to list the values we want to pivot on in the OrderYear column. The example is shown here:

SELECT
CustomerID,
[2001] AS Y2001,
[2002] AS Y2002,
[2003] AS Y2003,
[2004] AS Y2004
FROM
(
SELECT CustomerID, DATEPART(yyyy, OrderDate) as OrderYear, TotalDue
FROM Sales.SalesOrderHeader
)piv
PIVOT
(
SUM (TotalDue)
FOR OrderYear IN
([2001], [2002], [2003],[2004])
) AS chld
ORDER BY CustomerID

Output:

CustomerID	Y2001	Y2002	Y2003	Y2004
1	40732.6067	72366.1284	NULL	NULL
2	NULL	15653.6715	12118.0275	4962.2705
3	39752.8421	168393.7021	219434.4265	51925.3549
4	NULL	263025.3113	373484.299	143525.6018
5	NULL	33370.6901	60206.9999	20641.1106

More Rows .....

hope This example will help you to understand about concept of pivot operator