This article will go over how to transform distinct column values into columns and transform columns into distinct column values using the PIVOT and UNPIVOT function. There are 2 different ways to use these functions – static and dynamic. The static approach is used when the distinct columns or column values are known. If the query gets an extra unique column or value after the initial query then the new column or value will not reflect in the result set. Whereas the dynamic approach is the exact opposite, as there is no worry about new columns or values being added after the initial query.
First I’ll go over the static approach to explain how the functions are structured. Please note that these functions are available in SQL Server 2005 and later with a compatibility level of 90 or higher.
PIVOT Syntax – Static Approach
UNPIVOT Syntax– Static Approach
This syntax and more complex examples can be found here.
Below are simple examples of both functions looking at employee sales per year.
Dynamic PIVOT and UNPIVOT Query
To take a static query for both functions and make them dynamic, any hardcoded columns in the main SELECT and IN clause need to be moved into variables. Please note that if you’re using varchar variables to store the query, there is an 8000 character limit.
These are basic examples of the static and dynamic PIVOT and UNPIVOT queries that can be extended to meet your needs.