T-SQL Tutorial - PIVOTing Made Easy

hello and welcome to my channel I am bearded dev this is a T sequel tutorial and it's going to be all about pivot so there are two types of pivot operation there is many to one where we take many values apply an aggregation to those values and just return one result so we'll be applying an aggregation to the data and then there is 1 to 1 in which case we still use an aggregation but it is redundant in that case all we are simply doing is transferring values from rows to columns and I'll go through both of these examples shortly the pivot operation can be broken down into 3 steps and they are grouping spreading and aggregating and we'll talk about those a bit more as we go through the examples if we take a look now at the pivot syntax as we can see it's quite extensive it includes a derived table as an input to our pivot and then the pivot itself which includes an aggregate function the function the column we're actually aggregating the spreading element so that is the row values that are becoming columns and then a new column names their target columns so hopefully this will become apparent when we go through some examples a key point to note here is we spoke about previously Letty does it involve grouping but you'll notice that isn't actually part of the syntax and when we go through the examples we'll talk about exactly why that is the main reason for me doing this video is the syntax for pivot has been quite alien to me I think it's quite different to other elements of or operations in sequel server it's been a syntax for me that's hard to remember I often find myself having to Google how to remember how to write Vivat query although I understand and have a very good understanding of what the expected results are I often find myself having to to try and remember this intact so what I'm going to talk you through is how you can create some sample data yourself to practice but also the steps are you go through to break this process down and make it easier for me to remember so we're going to go through some simple examples but they can also be applied with some complex logic what we're not going to talk about in this video is dynamic pivoting it's quite an advanced topic but I will do a video in the future covering a dynamic pivoting right now let's head over to a sequel server management studio and first of all we'll get started on creating some sample data and then we'll go into both examples of many to one and one to want and you'll find the scripts for both of those if you wish to follow along in the description below now we're in sequel server management studio and I've got a relatively simple script here all I'm doing initially is checking if this underlying table location sales exists if it does I'm going to drop that table I'm then going to create the table location sales and it's going to have three columns it's going to have location which is going to serve as our grouping element we're going to be using date which is going to be used as our spreading element so those are the values in the rows that are going to become and new column values and then we've got an amount which is what we're going to a great and underneath that I've just got a simple insert statement so I'm from the UK I've just chosen three cities from the UK Birmingham London and Manchester I've put in some data against some dates and then we're just going to insert that and that's going to serve as the source for our pivot examples so if you wish to try this yourself we need to think of three things we need to think of something we can group the data by something where the rows are going to become columns which is going to be spreading element so date is a particularly good example for that and you'll say shortly I'm going to just be extracting the year from that to look at an aggregated level and finally we'll need something to aggregate by so try and think of other examples you could use within your data itself you don't have to create this you might find this data exist within another table but just find something you can group by something you can spread by which is effectively you could think of that as another grouping and then something you can aggregate such as an amount or quantity something along those lines so we're going to go ahead and execute this query now and I'll just open a new query window and initially we'll just start having a look at selecting the all columns from the table location sales so if we go ahead and execute that we can see in our results grid we've got our locations we've got some dates which are in date format and we've got our amount as well so now what we're going to do now we've got our sample data to practice with we're going to go through the steps that I normally follow to enable me to understand the pivot syntax and be able to write be able to build on this to write even more complex pivot statements so those steps are I'm just gonna write them out and number them here so initially what we want to do is create a derived table and that's going to serve as the source the input to our pivot then we're going to actually go ahead and create pivot and then finally step three is just going to be creating select I'm going to reorder those now too much I would write out this query so our select is actually at the top and I'm going to use this selector all as initial saw so I'm just going to cut that I'll hide the results grid for now and that's going to serve as a source for our derived table so I'm going to go ahead and select the columns which are location and notice I am putting these within square brackets or braces as location and date are both recognized as keywords in sequel server so if I go ahead and execute that query we've got the same results as before and what I want to do with date these just simply extract the year so I'm gonna use the year function I'm just gonna give that an alias as a year so if we look at those results now we can see we've got our location of Birmingham we've got a year of 2017 and then we've got two values so this is what I was talking about without without many two want example so we have many amounts against both the location of Birmingham and the year of 2017 and that's the same case for some of the examples we've got London in different years as well so that's going to serve as the import that's going to be a derive table so now what I like to do is just wrap that with in parentheses and I simply like to indent that as well to make sure that is actually we can see clearly when we look at the query as a whole that that's something that's effectively a sub query or derived table which ever term you like I guess derive table is the correct term for it but also within here as long as we are returning a result set that can be passed into pivot we can do anything we want in here we can add groupings we can add joins you can select from multiple tables as long as that serves the columns we need for our pivot so we're then going to go ahead on what I do with in step two is I'll write the key word pivot and then we open our parentheses and this is where we apply our aggregate function in this case what I want to return is the location and then I want the ears to become the columns and the aggregated amounts to sit within the values area so I'm going to say some amount so that is some is our aggregate function and amount is our aggregate column and then we're going to write four so this is where we're actually defining a spreading element now so as a spreading column is year and then we know it's right in open parenthesis again and now we're going to indicate the target column values so within my derived table I'll just quickly demonstrate I have the different values 2017 2018 2019 and 2020 and they are going to be the new columns so as they're integer values I also wrap those within square brackets or braces or parentheses so I'm just going to write those out there and then I'm gonna close that in so these are my target columns and then year is my spreading element so I'm just going to close the pivot and again I'm just gonna alias that as P for pivot now you can see building the query this way we are gonna get some red squiggly lines to indicate to that sequel server doesn't understand what we're trying to do and that's fine at this stage I would expect that to happen I'm writing out this query as I understand it rather than how a machine would understand it and in the final part step 3 is we're going to create select so then to write the keyword select and I'm going to select location and location is important within this query I'm just going to zoom out slightly so we can see that all within one window so location is important because it's part of our derive table but not part of our pivot and that is actually our grouping column so how this works is any columns that are part of the derive table here that are not part of the pivot here we can see amount and year sequel server recognizes those as grouping columns so it understands that what we want to group by and then we're going to write out again a new column names which are going to be the Year values and 2020 and then we're also going to write from as we want to select from the derive table as the input to pivot and we can see they're now looking through that query I'll just zoom out again so I can get that all on one page looking through that query we can see that we now have no red squiggly line sequel server now understand the machine understands what we're actually trying to achieve so if we go ahead and execute that query we can see how results grid here so it has grouped by location there's now one row for each location year values have now become columns and then our amounts have been aggregated into the results area and we can also see what's interesting to note here is when there are no value so London doesn't have any amount values for 2017 that would automatically normal will automatically be added for for us so just hide the results grid and zoom in again so within this select as well there's a few things to note we don't have to select all of our new columns so we could just select location 2017 and 2018 as demonstrated here however that would be a waste because we're actually doing the work to create those additional columns here but it can be ideal for testing another note is that we don't have to add all of the values so if year included say 20 different values but we only wanted to look at the last three years we don't have to add all of those values in here so I could save that work and actually just indicate that those are the only years that I'm interested in an important note is if you do have a lot of values and you might be here typing out you know up to 50 hundred or even more values that you want to pivot by then you probably do want to go down the route of looking into how to do that dynamically how to return those values dynamically like I said that's quite an advanced topic that we won't cover in this video but I will look to do in the future so that is a good example of a many-to-one so we're taking in this case many amounts from our derive table and we're returning one result for each location and year we're now going to go on to have a look at a one to one which is just simply transferring from rows to columns so I'll have a look at how to do that again we'll use as a starting point just select all from location sales and I will again write out my steps I don't actually write out these steps as comments but it can be helpful to start with so the first step is always create derived table that serves as our input second step create pivot third step create select so we'll see in this demonstration a few differences compared to the many to one example I'll just drop that under there and I will move the step three to the top so now we're going to create a derived table and we're actually going to be using a derived table I thought this was a good idea to show you how you can apply manipulation in this input and the direct the results of the derived table are determining whether it's one-to-one to one or many to one so in this case we are doing a one-to-one example which means we're going to apply some grouping within the derive table itself which we're going to do now so we're just gonna go ahead and write out so again with one twist extract the year from date and in this case we're going to be summing our amounts within the derive table so it's not going to be happening within the pivot it's going to be happening within the input to the pivot and that's where aggregating we need to indicate what we are grouping by and in this case we need to add a year function as well to the group by so we'll go ahead and execute this query so we can see here we've got a lot smaller amount of values I'll just show you that so there's a query there we're grouping by location in year date so we have a lot smaller values in this case we only have one amount for each year and location and that's what's going to be serving as the input so we now continue as normal wrap within parentheses and I like to indent with a tab just to indicate that is a sub part of the query and then as normal we're going to go ahead and create our pivot throw the keyword pivot and this is where the difference between many to one one to one really applies so we have to use an aggregate function but the aggregate function doesn't actually mean anything it's not going to be grouping data together as we're using integer or numeric values here we could use some again but it wouldn't be there's only one value so it would just be summing one value which should always be the same result but typically what is used is Max or min so in this case we're going to use max I'm gonna say max amount so that's our aggregate function in our group column and again we're going to be applying this for year that's going to be in and then our new column values again 2019 and 2020 so max can also be applicable so pivoting can may not just be for numeric values if you have a generic table in sequel which is typically done where you have a different number of attributes you might use a generic table then it will have an you name and then the value so you can also pivot strings as well and that's where max would typically be used and what alias our pivot is P and then we'll go ahead and create our final Select so again we'll select the location which is our grouping column and then values and new new column names that we have created which in this case represent years and then always remember to write from so we go from a derive table which serves as input to pivot we go ahead and execute that and we should have exactly the same results as our earlier example I've taken off a couple of the years yeah so we've got exactly the same results for both queries the difference between the two comes from how we pass in the input within the derive table so in this case we're just passing one value per location in year and in the previous example the many-to-one we're passing in many values per location in year and doing the aggregation within pivot itself really hope you have enjoyed that video like I say if you want to follow along I will copy the code into the description below but I thought I'd just end on a question which is what operations are performed when pivoting data post your answers in the comments below also if you have enjoyed the video feel free to hit that like button or if you'd like me to do anything different let me know in the comments below subscribe to the channel if you haven't already and check out my other videos thanks a lot for watching

Channel: BeardedDev

Views: 4,100

Rating: 4.8775511 out of 5

Keywords: beardeddev, bearded dev, sql pivot, sql server pivot, ms sql pivot, sql server pivot example, mssql pivot, sql server pivot rows to columns, pivot query in sql, sql pivot rows to columns, pivot sql server 2012, sql server pivot query, pivot example, pivot mssql, ms sql pivot example, pivot query, pivot in sql server, pivot table sql, pivot table, pivot sqlserver, sql server tutorial, learn sql server, sql pivot query example, sql pivot unpivot, pivot column, sql server

Id: OuV7cES4Qcs

Length: 21min 28sec (1288 seconds)

Published: Wed Jul 08 2020