Performance Measurement using CASE Expression over PIVOT Operator in SQL Server 2008 r2

Abstract: Data aggregation using PIVOT operator is useful in many aspects by converting data from rows into column as per the common values. The execution can be faster by using CASE expression in SQL Server 2008 r2. So, CASE expression can be used in the place of PIVOT operator. Some of the major issues in PIVOT operator are the grouping elements can�t be defined explicitly. Thus, when database schema changes by adding new column in the existing tables results NULL value in the report. Getting row and column wise aggregated value not possible with PIVOT operator. At a time only one grouping function can be used i.e Different grouping functions can�t be applied in different pivot columns. These problems can be solved by using CASE expression. In this paper it is explained the difficulties observed in PIVOT operator and solved using CASE Expression.

Keywords: SQL Server 2008 r2, CASE expression, NULL value, PIVOT operator.