Pivoting Data with SQL

In this training session, Austin Libal, a trainer at Pragmatic Works, demonstrates how to pivot data in SQL to make it more understandable and consumable. Austin uses a movie dataset to walk through two different methods for pivoting data: the PIVOT operator and CASE statements. His goal is to show how SQL transformations can convert normalized data into a more user-friendly format, much like an Excel-style report.

Why Pivot Data?

Pivoting is valuable when dealing with business questions that require data to be presented in an aggregated, easy-to-read format. Instead of returning rows of results, pivoting allows values from a column to become new columns. This makes comparisons across categories simpler and more consumable for users who may not be SQL experts.

Creating a Sample Dataset

To demonstrate pivoting, Austin creates a temporary table named Movies. This table contains:

• Movie ID (primary key)
• Movie name
• Release year
• Director
• Genre

With sample data inserted, the dataset includes 10 movies across various genres. The business question Austin poses is: “How many movies in each genre were released per year?”

Method 1: Using the PIVOT Operator

Austin first demonstrates the PIVOT operator. He begins by writing a subquery that selects the release year and genre from the Movies table. This subquery serves as the source for the pivot.

Steps include:

1. Wrap the subquery as a source.
2. Apply the PIVOT operator, using an aggregation function—in this case, COUNT of genres.
3. Define which column values should become new columns (e.g., Sci-Fi, Drama, Crime, Action, Romance).
4. Use ORDER BY to display results by release year.

The result is a dataset showing release years with counts of movies per genre. For example, in 1994 there were two drama films, while 1997 had one romance film (Titanic).

Method 2: Using CASE Statements

As an alternative, Austin shows how to achieve the same pivoted result using CASE statements with aggregation. Instead of PIVOT, this method uses conditional logic within SUM functions.

Key steps include:

1. Write a SELECT query with release year included.
2. For each genre, create a SUM(CASE WHEN Genre = 'X' THEN 1 ELSE 0 END) expression.
3. Group results by release year to ensure correct aggregation.
4. Order results by release year.

The final output mirrors the pivot operator’s result, giving flexibility for users who may be more comfortable with traditional SQL structures like CASE.

Practical Applications

Pivoting data in SQL is especially useful when creating reports that need to be shared with non-technical users. It enables teams to quickly analyze trends and distributions across categories. Whether using PIVOT or CASE, both methods allow SQL practitioners to reshape their datasets into more actionable formats.

Learning More

Austin concludes by encouraging viewers to explore additional learning opportunities through Pragmatic Works. Their On-Demand Learning Platform offers courses dedicated to pivoting, unpivoting, and many other SQL operations. Pragmatic Works also runs SQL Boot Camps that guide learners of all levels—from beginners to seasoned professionals—through practical applications of SQL.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on SQL and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.