SQL Server training :- How to implement Pivot?

ArticlesBlog

Written by:


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 Replies to “SQL Server training :- How to implement Pivot?”

  1. Sukesh Marla says:

    The Best part in shiv sir's video is , He are never edit the video in case of any compile or run time error in the programm. Rather he shows us error and solve it…..This kind of explanation let us remember the topic for longer time 🙂

  2. Jericho says:

    Nice work sir, thank you for the help. 1st time I ever pivoted in SQL 🙂

  3. brnjnsvld says:

    What if we want both sum(amount) and count # of orders aka count(amount)? Also why can't I do:

    Pivot(count(*)…
    or
    Pivot(count(CustomerName)… {gives me invalid column name in the 2nd section}

  4. Anurag Nayak says:

    That is excellent…i just saw the pivot part….i could do the unpivot part myself…. 🙂

  5. Flash River says:

    Shiv, nice work… thanks

  6. Jitendra Mahapatro says:

    Pivot with out aggregate function is possible

  7. New York Junky says:

    You made it very easy to understand. Thanks a lot!

  8. TheRoxas13th says:

    This clearly explained the PIVOT function. Thanks!! 

  9. Shashanka S says:

    This is an excellent video. Thank you.

  10. Waldo Fattore says:

    thanks for the explanation, it was a very clear example

  11. Clay Vescera says:

    This video is freaking awesome! Thank you!

  12. Mubashir Gul says:

    excellent video….nicely taught…Thankyou 

  13. nuthouse99 says:

    Excellent tutorial

  14. krismaly says:

    Function PIVOT explained well. I enjoyed watching the video and recommend to others. Thanks for educating the community and appreciate your volunteer-ship and efforts in producing this video. Please keep publish few more videos.
    Thanks a bunch

  15. Amal Chacko says:

    Thank You 🙂

  16. Mervin Padayachee says:

    Best, Simplest and accurate explanation on the InterWEB .. Thank you

  17. Ram Kumar says:

    Sweet and simple….

  18. Adam Schneider says:

    Where talking exporting tens of thousands of records in mere seconds because of pivot my friend. extremely useful. thank you.

  19. Mauricio Roldán Ramírez says:

    Excellent!

  20. Yogesh C U says:

    Only for 2 values it is fine… What about if have N number values?… Do I need to write manually all columns in step 1… Is other way to workaround it

  21. D Gowtham says:

    Great video sir

  22. nitesh sharma says:

    Well explained sir🙏 thank you!

  23. Craig Hugerich says:

    Thanks!

  24. Zeze Andjr says:

    I like the content, but the word is PIVOT, a short "i" as in Pin or Pill, thus Pivot, not Piivot, just a side note.

  25. Zeze Andjr says:

    I appreciate the content, but the word is Pivot, a short / quick "i" and a short / quick "o", not like the word Boat or goat, it's almost pivet, just a side note.

  26. Zunaid Hossain says:

    Very nice… Thank you sir.

  27. Srikrishna Vanam says:

    Hi , Thanks for making this video.

    However, I have a question :
    Is it possible to generalise this code, in case we do not know what are all the product names is a huge list?

    Here it is a simple example: we have shirts and shoes ( KNOWN), but suppose from my data of product names I have more than 100 products, Should I write the pivot code with all the 100 column names?

    Thanks.

  28. danilchezz says:

    trying to figure pivoting for the last 2 days, I went to a dozen of sites and did not actually grasp the plonker until watching this video. Kudos for the explanation, sir!

  29. mohammad farhan says:

    Thnaks for Expalantion .it was very clear exapalantion

  30. Variety Stuff says:

    Thank u for the videos

  31. tjldsl says:

    Just found this now, but this is BY FAR the best explanation of SQL Pivot functionality. Thank you very much for a great video!

  32. Prasunjeet Soni says:

    tell me one thing .. if you have more than 100 product then u write 100 product hard code .. and day by day its increasing then your this method will fail .. please give solution for dynamic pivot

  33. Sumeet Vashishat says:

    explained nicely…

Leave a Reply

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