Version Requirements

Before implementing PIVOT or UNPIVOT operations, ensure you're environment meets these prerequisites:

1. The database must be running SQL Server 2005 or later.
2. The database compatibility level must be set to 90 or higher.

To check your database version and compatibility level, execute the following query in SQL Server Management Studio:

Converting Rows to Columns with PIVOT

Setting Up Test Data

Create a temporary table to demonstrate the transformation capabilities:

Start by calculating the total number of studants per class:

Class           StudentCount
--------------- -------------
Grade8 Class1   5
Grade7 Class1   4
Grade9 Class1   3
Grade9 Class2   2

Your First PIVOT Query

Now transform the row-based class data into column-based display:

Header         Grade7Class1  Grade7Class2  Grade8Class1  Grade9Class1  Grade9Class2
-------------- ------------- ------------- ------------- ------------- -------------
Class Totals:  4             0             5             3             2

Notice that Grade7 Class2 shows 0 since no students belong to that class, which matches the expected behavior.

Syntax Breakdown

PIVOT requires two main table definitions in the FROM clause:

• Source Table: The inner SELECT query providing the base data
• Pivot Table: The outer transformation result

General Syntax:

Advanced PIVOT Example

Calculate gender distribution within each class:

Class           MaleCount  FemaleCount  TotalCount
--------------- ---------- ------------ ----------
Grade8 Class1   4          1            5
Grade7 Class1   3          1            4
Grade9 Class1   2          1            3
Grade9 Class2   1          1            2

Converting Columns to Rows with UNPIVOT

Syntax Overview

UNPIVOT performs the reverse operation, transforming columns back into rows. The syntax differs slightly from PIVOT as it doesn't include an aggregate function:

Store the PIVOT result in a temporary table for clearer demonstration:

Transform the gender breakdown query results back to a row format:

UNPIVOT requires all columns in the IN clause to have compatible data types. When columns have different types or sizes, the operation will fail:

INSERT @EmployeeData SELECT 'Tom', 24, 5432.1; INSERT @EmployeeData SELECT 'John', 26, 5678.9;

-- This will fail with a type conflict error -- SELECT EmployeeName, Attribute, AttrValue -- FROM @EmployeeData -- UNPIVOT(AttrValue FOR Attribute IN (Age, Salary)) AS Result;

-- Solution: Convert all columns to compatible types SELECT EmployeeName, Attribute, AttrValue FROM ( SELECT EmployeeName, CAST(Age AS DECIMAL(8,2)) AS Age, Salary FROM @EmployeeData ) AS SourceData UNPIVOT(AttrValue FOR Attribute IN (Age, Salary)) AS Result;

</div>Results:

EmployeeName Attribute AttrValue

---

Tom Age 24.00 Tom Salary 5432.10 John Age 26.00 John Salary 5678.90

When columns have incompatible types (such as INT and DECIMAL), explicitly cast them to a unified data type before applying UNPIVOT.