PIVOT in Sql server 2005

With the new PIVOT operatorin SQL Server 2005, the CASE statement and GROUP BY statements are no longernecessary. (Every PIVOT query involves an aggregation of some type, so you canomit the GROUP BY statement.) The PIVOT operator provides the samefunctionality that we tried to achieve with the CASE statement query, but youcan achieve it through less code, and it is a bit more pleasing on your eyes. Listing C is a sample PIVOT query tomimic our CASE statement query.

The meat of this query iscontained in the parentheses after the PIVOT operator. Inside the parentheses,we are using a SUM aggregate function to sum up the SalePrice for each Productlisted in the FOR Product IN() statement. This statement may seem somewhat oddbecause the values in the IN() statement are not enclosed in tick (') marks tosignify that they are literal values. These values are actually treated asColumnNames, and they are transformed into tabular fields in the final resultset.

If you run this query, youwill get a result similar to the one from our CASE statement query. The bigdifference is that this query still returns one row for each row listed in ourtable, which is not desirable; we want a distinct list of rows for each year ofsales listed in our table. The problem lies in the way we first constructed ourPIVOT query.

In the SELECT statement inthe query, we simply listed the year and a field for each product listed in ourSalesHistory table. The problem is that the SELECT statement that precedes thePIVOT clauses cannot specify which columns to use in the PIVOT clause. The PIVOT clause applies the grouping to all columns that are not the pivoted or aggregated fields. We can use a subquery to achieve the desired results. Listing D features the resulting query. This small change allows usto mimic our CASE statement query with less code.
Limitations

The PIVOT operator is usefulin many situations, yet there are drawbacks. Much of the functionality providedby the PIVOT operator requires you to "hard code" the fields that youare pivoting into columnar data. You can overcome this by building the PIVOTquery through dynamic TSQL statements, but this is not the most desirablesolution.

Another potential drawback ofusing the PIVOT clause is that you can only aggregrate one field in the resultset; in the CASE statement queries, you can aggregate as many fields as needed.

While these potentialsetbacks may seem like a big deterrent from using the new PIVOT operator, itstill has its place in the TSQL developers' toolbelt.


Example:

First we see a simple output from simple Select Statement


PIVOT



Then We are going to Pivot this table

PIVOT