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.

SELECT ,
    [ pivoted column] AS ,
    [ pivoted column] AS ,
    ...
    [ pivoted column] AS 
FROM
    ()
    AS 
PIVOT
(
    (column)
FOR
[]
    IN ( [pivoted column], [pivoted column],
    ... [pivoted column])
) AS 
[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