| Essential SQL

Hi, this is Chris and welcome to another SQL Minute. In today's episode, I'm going to share with you a guide I recently created, that shows you how to create a pivot table in six steps. Many of you may not know what a pivot table is. It's essentially, also known as a cross tab. And it's a way of summarizing values by rows and columns, and it creates a chart. Usually when creating a pivot table, what I tell people is, first we got to state what our goal is. Click here to learn more about my exciting new program FearlessSQL! https://www.essentialsql.com/go?utm_medium=youtube&utm_source=youtube&utm_campaign=FearlessSQLPreLaunch&utm_content=video So in my example, the goal that I'm going to aim towards, is to summarize parts located in a specific plant location. And summarize the quantity of those parts by production line. And then what I did, is I sketched out what I wanted my pivot table to look like. So you can see using the Venture Works data, what I came up with is I have my product lines. I wanted to have those as the columns. I'm going to sum up the quantity of parts in the cells, think of this like a spreadsheet. And then the location name will be the individual rows of my pivot table. And this is just kind of helps visualize what I'd like to see for my data. So when it comes your turn to create a pivot table, you can use this chart I put in the guide to help you understand what your columns would be, rows and what you're summarizing. In my mind, I think the hardest step in the pivot table, is actually identifying where the source of the data comes from. In my example, I'm actually pulling data from three different locations. You can see here where I'm going to be pulling information more centrally from the product inventory to get the quantity, but I have a product ID and a location ID, which are just numbers and they're not going to be fun to read, so I want to get the names of the location and of the product line. So to do this, I will incorporate joints and join in by primary key to get the location name, as well as the product line. The step I'm going to do here then, is to draw out the table relationships, understand what they are and then I'm going to go in and write a query just to get that data. So what I mean by that is, is let's just take this query here that I wrote. And I'm going to switch over to sequel server here and run it. Oops, I got to get the right database, hang on. And you'll see where I'm bringing in just un-summarized data. Here's an entry in the database for where the inventory is in the tool crib. There's 408 of them and it's for a product line that's null. And as you move on through, you're going to see more of this where it's the location name, the quantity. But notice how there's subassembly listed several times. And you'll definitely see product line listed several times. So the data is not summarized. This just the raw data.