Using Pivot Operator in SQL- Server

PIVOT can be used to generate cross tabulation reports to summarize data as it creates a more easy understandable data in a user friendly format.
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output
i.e it rotates a rows to columns and aggregations where they are required on any remaining column values that are wanted in the final output.

Syntax for the PIVOT operator:

SELECT <non-pivoted column>,
    [ pivoted column] AS <column name>,
    [ pivoted column] AS <column name>,
    ...
    [ pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(column)
FOR
[<column that contains the values that will become column headers>]
    IN ( [pivoted column], [pivoted column],
    ... [pivoted column])
) AS <alias for the pivot table>
[ORDER BY clause];

Example:
Suppose  in the database table "CREDITCARD" , there are two columns [CARDTYPE] and [EXPYEAR] that hold the data for the cardtype and the expiration year of
the credit card . A report on count of the no of credit card expired in year 2007, 2008 and 2009 of each card type is needed. In this case the query will be

 SELECT CARDTYPE, [2007] AS EXP_IN_2007, [2008] AS EXP_IN_2008, [2009] AS EXP_IN_2009

 FROM

 (SELECT CARDtYPE,EXPYEAR FROM  CREDITCARD)
 PIVOT
 (COUNT(EXPYEAR) FOR EXPYEAR IN ([2007],[2008],[2009]))

 ORDER BY CARDTYPE

Here is the number of records in the table

And here is the report acording to our requirement