SQL Tutorial - How to create a Dynamic Pivot in SQL Part 2

hello and welcome to my channel hope everybody is safe and well welcome to how to create a dynamic pivot query part two this is going to be building on part one in fact we're going to be creating exactly the same results as part one but i'm going to show you a more modern way of building a dynamic pivot query so if you haven't checked out part one there will be a link coming up on the screen if you want to go back and check that out but this is the modern approach there's been a new function introduced recently that makes dynamic pivoting a lot simpler as always with my videos if you'd like to follow along the scripts are available in the description so we have here a simple script that i'm just going to throw some data into a table for us to work with and we can see we've got customer id product and amount in that table some perfect data for performing pivoting operations this is a starting point so this is a simple pivot we're just looking to calculate the total amount for each product so if i execute this query and show you the results we can see for each customer id we can see their total amount spent on each of those products but as we know with a standard pivot query it's quite static so the problem we're facing is we can add new products all the time i've got a little demo here just to show that so if i just insert an extra product of television and if i go back to my pivot starting point we can see that is not actually included in fact i now need to actually edit my query to include television in the results so i'll have to add it to the spreading elements and then also in the final select so if we are adding new products all the time and obviously pivoting isn't specific to products it can be anything but anything that we're looking to pivot by if that's constantly changing we're adding values then it's difficult to manage so we need a we need an easier way to do things so let's have a look at how we can build up a dynamic pivot query and in this video again like in part one we'll just be focusing on the dynamic building of the column list rather than the whole pivot query so i'm just going to open a new query window and our starting point as always is just going to be to get a row set of the products we're working with in this case and it's going to be a distinct row set so if i'm just going to select the distinct product from dbo orders and we can see there uh we've got nine products i'm actually just going to go back and reset this table so it doesn't it won't include television just so we can test out that results at the end so we've now got eight products the television isn't included and we'll be adding that later so once we've got our distinct result set of products we then spoke about a function in part one called quote name and this allows us to add in character surrounding our string and the default is square brackets and i'm just going to alias that as product so we don't lose that name so we can see there we've got our result set of products within square brackets or square parentheses now once we've got that there's new function available uh i believe it was released in sql server 2017 uh and because azure sql database is always up to the latest version as well it will be available in there but i will put a link to the microsoft docs in the description so there's a way there's a function to help us build a character separated list of values so let's have a look at that and to use this function we're actually going to be we want the distinct products to be the input we don't want a massive list so i'm actually just going to turn that into a derived table which i'll build out shortly

so the function is string underscore ag and we can see here we can add an expression and a separator which will return in varchar max so the expression is going to be product and then the separator is just going to be a comma that's going to come from and then we're going to move this into a derived table and the reason we're using a derived table is we want the distinct operation to be performed before the string aggregation we just need to add an alias i usually alias my derived tables as d so if i execute that query now we can see compared to part one we have got to a point where we've generated this list very quickly and very simply so that's a new powerful function available in uh in microsoft sql so now we need to build out a dynamic query which once we've got this column list is actually quite straightforward so i'll just hold that for now and we're just going to declare two variables one will be representing the query and the other column list just going to change this to select columns equals so that's going to set our column value to the result of our string aggregation and then we're going to build out our query so we're going to say set at query equals well open apostrophe it's going to be select customer id and we can really use our pivot start of what we're doing here so this is going to be a column list and so everything else around it we just need to build up so that's how i'm building this query so it's probably good to always start with a static query and then look at how you can make that dynamic it's quite hard to write dynamic queries from scratch but if you can great so that's going to be a comma and then we're going to close that off and then we're going to add in our columns so that's this part here that we're building so the columns is going to represent from here to here then we need to go back to our string builder and say from this is where we create a derived table so that's going to be customer id a derived table within the pivot customer id product and amount from dbo orders go back to pivot start so we've now built to there and then we're going to add in a pivot open our parentheses some amount we want the total amount for product in open parentheses uh close off our stream builder and in our column list again open the string builder close off the parentheses close off our pivot and then we're going to order by customer id we don't need that it's just so we can see the results a bit more clearer so again going back to pivot start we've saved the hassle of typing out this part of the query and then we just need to execute using sql passing a query variable execute and now we can see the results on screen as expected we'll do a final test of adding in a television product come back here we're not going to make any changes we're just going to expect

a dynamic building of that column list to pick up that new product execute the query and we can see that television has now appeared as part of our pivot so really hope you have enjoyed that video as you can see string aggro function makes it a lot simpler than what we were doing in part one and if you are using sql server 2017 onwards or as your sql database this is a much simpler approach it's a lot more concise and easy to understand actually what you're trying to build when we're using the number of xml functions it's hard to understand exactly what's going on but this is quite straightforward once again the final query will be available in the description as i know it doesn't display on screen very well particularly when it's quite long let me know your thoughts in the comments below is there any more videos you'd like to see on pivot or any other areas of data engineering or data analysis thanks a lot for watching