Previously we are using CASE,GroupBy In place of this concept Sql Server 2005 provide
With the new PIVOT operator in SQL Server 2005, the CASE statement and GROUP BY statements are no longer necessary.
(Every PIVOT query involves an aggregation of some type, so you can omit the GROUP BY statement.)
The PIVOT operator provides the same functionality that we tried to achieve with the CASE statement query, but you can achieve it through less code
MSDN states that you cannot pivot two columns
If you want two columns with the same column header, then you cannot achieve it
The PIVOT operator is useful in many situations, yet there are drawbacks.
"hard code" the fields that you are pivoting into columnar data
You can overcome this by building the PIVOT query through dynamic TSQL statements, but this is not the most desirable solution.