Unpivoting Data with SQL

Introduction

Austin Libal, a Data Engineer Trainer trainer at Pragmatic Works, takes viewers through the process of unpivoting data using SQL. Unpivoting is a handy technique to transform data from a denormalized state to its normal form, facilitating easier analysis and a fresh perspective on the information.

Creating the Movie Sales Table

• Austin demonstrates the creation of a new table named "movie sales" with columns for ID, title, and several years of sales data.
• Sample data is inserted into the table to simulate a denormalized dataset that needs unpivoting.

The Denormalized Data

• The queried results reveal a denormalized structure with separate columns for sales in different years, making analysis challenging.

Unpivoting with SQL

1. Defining Columns for Unpivoting: Austin selects the relevant columns for unpivoting: movie ID, title, year, and sales amount.

2. Subquery for Source Data: A subquery is used to fetch the necessary columns from the movie sales data, creating a source for the unpivot operation.

3. Executing the Unpivot: The unpivot operation is initiated with the command, transforming columns into rows. Austin explains the syntax, including the unpivot keyword, the new column (sales amount), and the values to unpivot (sales amounts for different years).

4. Ordering the Results: An order by clause is added to arrange the results by movie ID and sales year.

5. Executing the Unpivot: Austin executes the code, successfully transforming the denormalized data into a normalized form.

Comparing Results

• Austin retrieves data from the newly unpivoted table, showcasing the transformation's effectiveness.
• A side-by-side comparison with the original denormalized data highlights the clarity achieved through unpivoting.

Alternative Approach: Union All Method

• Austin introduces an alternative method using the union all statement to achieve a similar unpivoted result.
• The union all method involves multiple select statements for each year, providing flexibility in combining data.

Closing Thoughts

• Austin emphasizes the versatility of unpivoting, showcasing its application in transforming data for better analysis.
• Viewers are encouraged to explore more SQL content on the channel, with promises of upcoming videos and advanced SQL learning opportunities.

Conclusion

In this insightful tutorial, Austin Libal demystifies the process of unpivoting data with SQL, making it accessible for both beginners and experienced users. The step-by-step demonstration, coupled with an alternative approach, provides a comprehensive understanding of how to tackle denormalized datasets and unlock their full analytical potential. As Austin wraps up, viewers are left with a newfound appreciation for the power of SQL in shaping and optimizing data for meaningful insights.

More From Pragmatic Works

Viewers that enjoyed this video and want to learn more about Azure SQL we invite to subscribe to the Pragmatic Works YouTube channel. We also invite our viewers to sign up for the Pragmatic Works' on-demand learning platform, where we offer a wide range of Microsoft courses across many Microsoft applications.