Working with the PIVOT operator

A PIVOT operator, simply put, transforms a table output column into rows. It rotates a table-valued expression (a table-valued expression returns output as a result set/table) by turning unique values from a selected column into multiple columns and aggregates the remaining column values in the final table output.

Figure 7.1 – Basic understanding of the PIVOT operator

The PIVOT operator is very similar to a CASE statement, but much simpler and more easily readable for the user.

Here is the syntax for it:

SELECT
<Unique Column Name(s)>
FROM
<SELECT query to produce data from table(s)>
PIVOT
(
<column to be aggregated, e.g., COUNT, AVG, etc.>
FOR
<Unique Column values that will become column headers>
);

Important

For aggregate functions in the PIVOT operator, any null values in the value column are not considered during computation.

Let’s walk through an example to get a...