![]() And you’ll definitely see product line listed several times. But notice how there’s sub-assembly listed several times. There’s 408 of them and it’s for a product line that’s null.Īnd as you move on through, you’re going to see more of this where it’s the location name, the quantity. Here’s an entry in the database for where the inventory is in the tool crib. Oops, I got to get the right database, hang on.Īnd you’ll see where I’m bringing in just un-summarized data. And I’m going to switch over to sequel server here and run it. So what I mean by that is, is let’s just take this query here that I wrote. 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.īy doing this, I’ll have SQL put together for the pivot table source data. So to do this, I will incorporate joins and join in by primary key to get the location name, as well as the product line. 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. ![]() In my example, I’m actually pulling data from three different locations. In my mind, I think the hardest step in the SQL pivot table, is actually identifying where the source of the data comes from. 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. And this is just kind of helps visualize what I’d like to see for my data. And then the location name will be the individual rows of my SQL pivot table. I’m going to sum up the quantity of parts in the cells, think of this like a spreadsheet. So you can see using the Adventure Works data, what I came up with is I have my product lines. Sketch the End ResultĪnd then what I did, is I sketched out what I wanted my SQL pivot table to look like. And summarize the quantity of those parts by production line. So in my example, the goal that I’m going to aim towards, is to summarize parts located in a specific plant location. Usually when creating a SQL pivot table, what I tell people is, first we got to state what our goal is. And it’s a way of summarizing values by rows and columns, and it creates a chart. It’s essentially, also known as a cross tab. Many of you may not know what a SQL pivot table is. In today’s episode, I’m going to share with you a guide I recently created, that shows you how to create a SQL pivot table in six steps. ![]() Hi, this is Kris and welcome to another SQL Minute. ![]() How to Create a Pivot Table in Six Steps Transcript ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |