How to Pivot Your Data in T-SQL for SQL Server - Static and Dynamic Column Examples

how can i make a query an sql server that pivots my data i'm your host sean mckenzie thank you for joining me once again on my channel on data engineering in this episode we're going to take a look at using a pivot query in sql server and we're going to do it in two ways the first way is going to be a static way where we name our columns that we want and then we execute our pivot and the second way is a little bit more in depth and it shows how to make a pivot query with dynamic columns coming out and so without further ado let's get to our pivot in sql server looking to hire tech contractors for your project make sure to check out the links in the description okay so i'm using the adventureworks database here uh test database that i got from microsoft and uh i'll give you a sort of preview of the little data set that i'm going to use so i'm looking at these sales order header table and so that's going to be in the sales lt there and if i hit you know if i hit f5 and just look at the table you can see you know it's got 32 rows and it's got different dates there for sales we're going to pretend this is the mid-month sale and that has an online order flag and it has some totals down at the end here and uh the subtotal is the one that we're going to use since it's not including tax or anything because our purposes or you know our goal is to find out you know c in a pivot how did our store sales compared to our online sales for the mid-month sale and so if i look at the order date order on online order flag in the subtotal you can get them into a sort of a column list here and you know that's great looks fine but we want to pivot that and we want to see it where you know there's a column for you know zero and a column for one for the online order flag and also with some sum totals for each of the days and that's sort of what we wanna that's what we wanna see and so in order to do this the first thing we're gonna do is we're gonna create a source table um and so uh what we're gonna do is we'll say select star from and we'll go select inside of that we'll do select from sales lt.sales order header and we're going to select those columns that we looked at up above there so i'll select order date and the online order flag and i'll go back and put from in there uh and so we've got the order date and online order flag and then the subtotal which is the amount before our taxes and and our shipping costs and so what we're going to do is we're going to call that our source table so we'll we'll call that source and then we can do our pivot so after that we'll say pivot and we're going to choose the sum of this of the subtotal because that's what we want to see we want to see what's the sub you know the amount that we got for online and on and and offline orders uh for you know for each day for each of the mid mid month sales and so in order to do that we'll say for online order flag and then we have to statically you know hard code in the expected values there so if we know what those values are then the pivot is very easy if we know that that it's only you know one or two or three values that's in our say you know that are available for that particular field i spelled this wrong here and then we have to know what the values are that we're going to put in there which is fine if you only have a couple of values but if you have you know a big range or varying values that you want to see then you want to stay tuned in the video because i'm going to show you how to do dynamic video next so basically what you do is you say from my pivot and that's the the last statement that you do there and and then hit f5 and this is what you're going to get so so you've got your your pivoted values according to your online and offline orders and and that's great that's exactly what we want to see here now in the next section what i want to do is i'm going to show how to do a dynamic column uh column output which is uh what what i think a lot of people would like to to do with the pivot and sql server and so um what i'm going to do is i'm going to combine a couple of techniques one is called string underscore ag which is aggregate which is going to give us a list of those those values or you know categories that you might have in your table so we're going to declare a variable for that called dynamic columns as envar car max because it could be a big long list and then we'll declare my query as as also as an nvr car max and we're going to actually create the sql for that at the end of this query here after we load some variable after we load the dynamic column variables and so i'll use a cte because i want to get the distinct values from that table of all of the different categories and in this case there's only two but i'll say select distinct online order flag from sales lt dot sales order header and that's going to give me all of the um you know the values in there um in distinct ways so that when we when we put them together in a list it'll it won't give duplicates and so if you do this query that i'm about to do without doing the distinct part of it it'll complain it'll give an error saying that you know you've got more than one zero column or more than one's in one column or two column or whatever however many categories you might have and so then we can load the variable so we can go select distinct dynamic columns and we'll go string aggregate which is sql server 17 and higher or azure if you're doing sql server on azure it's the same and if you're looking to do the same thing using an older version of sql server i have a video that shows you how to do the string aggregate part of this which i will i'll link up above there so in case you have an older version of sql server so but in this case we'll say we want to create that field just like we did in that up above there where you've got the zero one in the static one we're going to concatenate you know the left square bracket the category and then the right square bracket using a comma every possible value and then we'll say from the uh you know our our uh our cte that we created called dst so now if i just show you i can say you know select dynamic columns and if i just run that it's going to show you what the output is and so see how this dynamic output here it has the same as our as our static you know query but this could have many many different columns in it and so this allows us to to go ahead and and continue and create our query using all of these columns here and so in order to do that i'm going to set my query variable equal to and then we'll select that order date just like we did in our static query we're going to build a query string this time instead of hard coding the column values in so we're going to say select order date comma and then we're going to do our dynamic columns which you see down in the result window there so it's in this case it's going to go 0 comma 1 but you might have you know abc or something like that or you might have different columns all kinds of different names for your categories and so we'll say from and then we're going to do the same thing so we'll go select you know order order date and then online order flag and subtotal uh you know the subtotal being the amount without the uh without the tax and and stuff and then we'll go from sales lt sales lt dot sales order header and uh that's the same as what we've been using so far and uh and then we'll continue on and we'll order we'll put in our source and pivot just like we did before um so this that's going to be our source table just like we did before except now we've got you know dynamic columns there's our source just like we did in the previous one and uh and we're gonna go ahead and now that same query has the columns all those dynamic columns in it and now we can pivot and we can you know based off of those columns we can do our sums and so we'll go pivot and then inside of here we're going to do our sum so i'll go ahead and just create another row here and we'll tab that over to make it look uh so that it's you can see it pretty clearly and then there we go we go sum uh subtotal and then um we'll say a four online [Music] for online order flag and uh and then and then we'll put in our columns so we'll say in uh you know zero you know our dynamic columns basically 0 and 1 in this case but it'll be you know however many columns that you have and so we'll add that in for dynamic columns and and then that's going to be our you know my query and so it's just going to give the sum make a note that you can also do average and all kinds of other things inside of here you don't you know min and max and those types of things i'm just using some here but you can use different uh summary types there so okay so there we go we've got from my pivot and we'll we'll close that out and then all we have to do is is say execute my query and then close it out with a semicolon and then we can select the whole thing and this should go if i haven't screwed up anything here we'll see my query so there's my query it's going to use dynamic columns and that we created using string ag and then and then there's my query that we've created as well so we're going to select the whole thing and hit f5 and there we go there's the same result that we got in the first query except this time the columns were auto generated and so you could use the same type of technique uh for the categories in your uh in your query and i should note that uh this is you know for sql server 2017 and higher and if you'd like to see the example with older versions i can do that that's our dynamic query our static query and that's how you can do a pivot in sql server need help for coaching on your project make sure to check out my patreon the link is in the description hope you enjoyed today's discussion on how to do pivots and sql server if you like what you saw please give the video a thumbs up make sure to subscribe to the channel if you haven't subscribed yet click the bell when you see the bell and if you have any questions or comments put those in the comment section below have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 59

Keywords: how to pivot data in sql server, tsql pivot, sql pivot, pivot sql server, sql pivot dynamic, tsql pivot dynamic, sql server pivot dynamic, string_agg example, pivot queries sql server, sql server pivot queries, pivot tsql, sql server, pivot in sql server, sql pivot rows to columns, sean mackenzie data engineering

Id: KHNl4pXqYAI

Length: 13min 0sec (780 seconds)

Published: Fri Dec 10 2021