SQL Server training :- How to implement Pivot?
Articles,  Blog

SQL Server training :- How to implement Pivot?


In this video we will try to understand how can we implement Pivot and Unpivot in SQL Server now before we go ahead and we try to implement Pivot in SQL Server first let’s try to understand what exactly a Pivot means so in order to understand the Pivot function you can see that i have created a very simple table here which has three columns 1 is customer Name Product Name and amount and you can see that i have couple of record here saying Shiv has bought shoes for 100 Rupees then Shiv has bought Shirts for 200 rupees Raju has bought shirts but Raju has not bought any kind of shoes and then we have Ganesh who has bought shoes and shirts and and we have Jagnish who has bought only shoes i have very simple table here which tells which customer has bought which product and which amount now let’s say that we want to visualized data from different perspective for example let me just open this Excel over here so let’s say that you want to say that can you tell me how many customer have bought shoes and how many customer have bought shirts for example shiv has bought shoes that is 100.23 there is not any 23 paisa as such this is just hypothetical example and again if you see Shiv has bought shirt that is 200.34 then we have Raju Raju has not bought shoes right so this is 0 and Raju has bought shirts that is 145.67 so you can see basically this row wise data we have actually change the perspective and made it column wise so now actually i am seeing into section of two data points for example shoes is bought by shiv at this much amount then Raju has bought shirts so in other words Pivot means basically where we see into section of two data points and we see some kind of summarize information now the next question is how can we implement this in SQL Server so the good news is that we have very nice ready made function in SQL Server called as Pivot function by using this Pivot function we can change the row wise data into Pivot data so let me go and show you step by step how we can go and change this data into a Pivot data now before i go ahead and demonstrate the Pivot code let me go and explain the Pivot code because if i start demonstrating the Pivot code now itself it can lead confusion because the code is bit big so what i will do is let me first explain you the Pivot cod and then we will go ahead and executed this code in a our SQL Server manager now you can see there is a big image diagram which is displayed on the video and this is the Pivot code this complete thing over here is a Pivot code so what i have don is i have divided the Pivot code into three section you can see 1, 2, and 3 so let’s start with the first step in Pivot code so in the first step we start with a select statement you can see that we have a very simple select statement over here and this select statement has those column names which we want to display on the Pivot report so for example in our Pivot report we want to display customer name shoes and Shirts so the same column name will come in this select statement so the first part of the code of the Pivot code start with the select statement and it has those column names which has to be displayed on the Pivot code report now the second part of the code actually get’s the actual data which is needed for the Pivot function so our actually data is nothing but these rows if you remember in this previous part of the video i had displayed the table which had shiv, Shoes 100 Rupees Shiv Shirts 200 Rupees so that actually data is fresh by this second section so the first section actually displays the column names for the Pivot report and the second section actually get’s the actual data now let’s talk about the third section now the third section of the code is where all the action happens this is the section where your Pivot function lies so you can see that in this third section we are saying here Pivot and the second thing is we are saying some of amount so this is the amount columns we want some of the amount but we want some of the amount using the product name like shoes and shirts so what is does is it actually says ok Shiv has bought shoes 100.23 and just goes and fill in this column so here we defined two things one is we define what kind of aggregation we want some count whatever it is and on what data item if you want we wanted shoes and shirts you can see i have defined your shoes and shirts so now what happen is basically he takes these from the first section it takes the column name from the second section he takes the data and from the third section the Pivot function actually goes and translate these rows into columns put the summery amount so this was the basic theory of how the Pivot code looks like now let’s go ahead and execute this Pivot code and see this all this thing in actual action so let’s ho ahead and do the actual practical the complete code is divided to 3 part step 1 step 2 let me just go and expand this here so that we can see the and step 3 so in step 1 we defined the column names for the Pivot in step 2 we get actual data and in step 3 we actually have our Pivot function so the first step is we will go and select the column names of our Pivot report so the first column in our Pivot report is the customer name second column we want is shoes and shirts these actual values will be the second column so i will say here shoes as shoes and a shirts as shirts let me just go and ended this this here because if you ended you can read it properly there it is so now the next step in next step we have to actually get the actual data so i will say here on these column should be Pivoted from this Pivot data so here i need to go and defined in this bracket over here i need to go and define the actual data so the actual data is Select product name product amount from this table so there it is so product name amount from the test Pivot table my table name here is the test Pivot so we have done step 1 define the column we have done step 2 which will actually face the data and the last step is we need to go and write the Pivot function so in this third step i will say here Pivot around bracket Pivot the some of amount amount means this is the column right amount column so some of the amount column and you want to Pivot this for now this comma is not needed here you want to pivot this for product name right in now what is the column there are 2 columns we have one is shoes and the other one is shirts and all these column should be in a bracket we will say this is the actual Pivoting so the first select statement give is the column name the second select statement actually gives you the data and the last Pivot function actually tell you know that how do you want to Pivot the data in other words what is aggregation is it some amount and on which values so done let me just go and do the check syntax here what is this in correct syntax ok this bracket should actually go here it is not after the Pivoting so it should be here and let me do a checks syntax look to define let me just go and delete this stop SQL over here so there we are let me do a check syntax looks fine and let me say execute it says invalid customer name but i do have a costume name column here i do have a column name called as a customer name ohhhh ok see now in the data we have not selected the customer name we need to select the customer name here because at the end of the day it should come form the data right so this customer name is not here that is good and the product name is not to this columns that is good check syntax say execute i have just become old guyz so for getting syntaxes here so there it is now so if i go and do select * from this table over here very quickly so that we can do comparison so there it is you can see very quickly let me just pull this up so that is our actually row wise data and you can see now the below table or the below this way actually shows how he has a shown it in a column wise so that was a very simple example of Pivot function now what i would expect is the Unpivot i will not show you over here i leave it at a Homework to you guyz so you can do the Unpivot function So i hope that you are enjoy with this video in this video we talk about what exactly Pivot function and then we also did small demonstration of how exactly Pivot function works THANK YOU SO MUCH 🙂

33 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *